mtuepker
asked on
Replace null value in SSIS with value from previous row?
I am pulling in a flat file that formatted like the following example. Basically there is a column that has an ID in it and that a few rows about that ID. I want to import the data using SSIS that will pull add the ID in every row.
Current:
cardholder balance1 balance2 date
12345 1.11 1.11 102010
0 0 92010
0 0 82010
123456 23.33 23.33 102010
0 0 92010
Want to be:
cardholder balance1 balance2 date
12345 1.11 1.11 102010
12345 0 0 92010
12345 0 0 82010
123456 23.33 23.33 102010
123456 0 0 92010ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry, didnt mean to give it a C rating...too early in the morning to be playing on EE apparently. What I ended up doing (thanks to a time crunch) was just importing the file in w/ it as null and then running this:
SELECT *,
ISNULL(cardid, (SELECT TOP 1 cardid FROM table WHERE ID < t.ID AND cardid IS NOT NULL ORDER BY ID DESC))
FROM table t
to get the IDs as I needed them.
SELECT *,
ISNULL(cardid, (SELECT TOP 1 cardid FROM table WHERE ID < t.ID AND cardid IS NOT NULL ORDER BY ID DESC))
FROM table t
to get the IDs as I needed them.
ASKER