July 31, 2017 at 2:59 pm
| Name | Clinic Number1 | Clinic Number2 | Clinic Number3 |
| Jeff | 531-2531 | 622-7813 | 565-9901 |
| Laura | NULL | 772-5588 | 312-4088 |
| Peter | NULL | NULL | 594-7477 |
I have a table like this as above which is getting loaded from flat file in SSIS, I want to update the NULLs like below:
| Name | Clinic Number1 | Clinic Number2 | Clinic Number3 | |
| Jeff | 531-2531 | 622-7813 | 565-9901 | |
| Laura |
| 772-5588 | 312-4088 | |
| Peter | 531-2531 | 772-5588 | 594-7477 |
If there is NULL for LAURA in clinic number 1 assign the previous JEFF's clinic number and if clinic number 2 is null for Peter, assign the previous Laura's clicni numebr 2 value to peter and so on.
Can anybody help me with a code in sql?
Regards,
Shweta
August 1, 2017 at 5:14 am
shwets47 - Monday, July 31, 2017 2:59 PM
Name Clinic Number1 Clinic Number2 Clinic Number3 Jeff 531-2531 622-7813 565-9901 Laura NULL 772-5588 312-4088 Peter NULL NULL 594-7477 I have a table like this as above which is getting loaded from flat file in SSIS, I want to update the NULLs like below:
Name Clinic Number1 Clinic Number2 Clinic Number3 Jeff 531-2531 622-7813 565-9901 Laura
531-2531 772-5588 312-4088 Peter 531-2531 772-5588 594-7477 If there is NULL for LAURA in clinic number 1 assign the previous JEFF's clinic number and if clinic number 2 is null for Peter, assign the previous Laura's clicni numebr 2 value to peter and so on.
Can anybody help me with a code in sql?
Regards,
Shweta
You mention 'previous' as though there is some order to your data, but as far as I can see. it is unordered. To solve this in SQL requires an ORDER BY, to give meaning to the concept of 'previous'.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 1, 2017 at 8:25 am
Phil Parkin - Tuesday, August 1, 2017 5:14 AMshwets47 - Monday, July 31, 2017 2:59 PM
Name Clinic Number1 Clinic Number2 Clinic Number3 Jeff 531-2531 622-7813 565-9901 Laura NULL 772-5588 312-4088 Peter NULL NULL 594-7477 I have a table like this as above which is getting loaded from flat file in SSIS, I want to update the NULLs like below:
Name Clinic Number1 Clinic Number2 Clinic Number3 Jeff 531-2531 622-7813 565-9901 Laura
531-2531 772-5588 312-4088 Peter 531-2531 772-5588 594-7477 If there is NULL for LAURA in clinic number 1 assign the previous JEFF's clinic number and if clinic number 2 is null for Peter, assign the previous Laura's clicni numebr 2 value to peter and so on.
Can anybody help me with a code in sql?
Regards,
ShwetaYou mention 'previous' as though there is some order to your data, but as far as I can see. it is unordered. To solve this in SQL requires an ORDER BY, to give meaning to the concept of 'previous'.
Hi Phil,
Thanks for your reply. That is the problem here. I do not have ordered data. it can come in any form. but its actually always a set of three. Jeff, Laura and Peter. sometimes v get data for all 3 sometimes only two sometimes only one. So when there is no data for Jeff and data for Laura and Peter we have to assign the previous value of Jeff to to Jeff where it is missing. similarly to Laura and Peter. if there is data for Jeff and Peter then assign the previous value of Laura to current NULL Laura.
I guess this needs to be done in Script component in SSIS, but I am not sure how.
Regards,
Shweta
August 1, 2017 at 8:33 am
shwets47 - Tuesday, August 1, 2017 8:25 AMHi Phil,Thanks for your reply. That is the problem here. I do not have ordered data. it can come in any form. but its actually always a set of three. Jeff, Laura and Peter. sometimes v get data for all 3 sometimes only two sometimes only one. So when there is no data for Jeff and data for Laura and Peter we have to assign the previous value of Jeff to to Jeff where it is missing. similarly to Laura and Peter. if there is data for Jeff and Peter then assign the previous value of Laura to current NULL Laura.
I guess this needs to be done in Script component in SSIS, but I am not sure how.
Regards,
Shweta
The problem is, without any ordering you don't have a way of reliably achieving this. Even assuming that the clients coming in name order is better than nothing (if that is normally the case). However, if the order of your data is completely non-deterministic then there are no "previous" or "next" rows, there are just "other" rows and there is no concept of "where" they are sequentially.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 1, 2017 at 8:49 am
Thom A - Tuesday, August 1, 2017 8:33 AMshwets47 - Tuesday, August 1, 2017 8:25 AMHi Phil,Thanks for your reply. That is the problem here. I do not have ordered data. it can come in any form. but its actually always a set of three. Jeff, Laura and Peter. sometimes v get data for all 3 sometimes only two sometimes only one. So when there is no data for Jeff and data for Laura and Peter we have to assign the previous value of Jeff to to Jeff where it is missing. similarly to Laura and Peter. if there is data for Jeff and Peter then assign the previous value of Laura to current NULL Laura.
I guess this needs to be done in Script component in SSIS, but I am not sure how.
Regards,
ShwetaThe problem is, without any ordering you don't have a way of reliably achieving this. Even assuming that the clients coming in name order is better than nothing (if that is normally the case). However, if the order of your data is completely non-deterministic then there are no "previous" or "next" rows, there are just "other" rows and there is no concept of "where" they are sequentially.
Hi Thom,
This is actually data coming from flat file as source. I have a power shell script which is working with this logic. the one who did this power shell script has added a row id and incrementing on row id to identify the rows.
Below is the actual code I replaced HX1 with Jeff, HXH with Laura and HXT with Peter.
#Construct the new line which contains HX1, HXH, HXT records as single line
switch ($lineStarter)
{
"HX1" {
$HX1Line = $line
}
"HXH" {
$HXHLine = $line
$HXRLine = ""
}
"HXR" {
$HXRLine = $line
}
"HXT" {
$write.WriteLine([string]$rowid + "|" + $fYear + "|" + $fFileName + "|" + $HX1Line.TrimEnd(' ') + "|" + $HXHLine.TrimEnd(' ') + "|"+ $HXRLine.TrimEnd(' ') + "|" + $line.TrimEnd(' '));
$rowid += 1
}
default {} #Do nothing -- This can be extended in future if users decided to bring other codes.
}
}
}
Now my flat file data looks like this:
HX1V03E 000000AT520115243345220170720
HXH3984185631CV1973120700582644HCPP
HXTJ182B 0025500120170712
HXH9601304307PC1983020800582882HCPP066738
HXTJ138B 0048750120170713
HX1V03E 000000AT520281203345220170720
HXH6734502146CX1947032500582088HCPP072573
HXTX091B 0023580120170711
HXTJ157B 0032100120170711
My destination table should look like this: the HX1 and HXH values which are in bold are actually not coming through the file for that corresponding line but we have to assign the previous value like this:
| Rowid | Year | FileName | HX1 | HXH | HXT |
| 0 | 2017 | EG7081.1ddc3928f40645c58bef81fca3036241.423 | HX1V03E 000000AT520115243345220170720 | HXH3984185631CV1973120700582644HCPP | HXTJ182B 0025500120170712 |
| 1 | 2017 | EG7081.1ddc3928f40645c58bef81fca3036241.423 | HX1V03E 000000AT520115243345220170720 | HXH9601304307PC1983020800582882HCPP066738 | HXTJ138B 0048750120170713 |
| 2 | 2017 | EG7081.1ddc3928f40645c58bef81fca3036241.423 | HX1V03E 000000AT520281203345220170720 | HXH6734502146CX1947032500582088HCPP072573 | HXTX091B 0023580120170711 |
| 3 | 2017 | EG7081.1ddc3928f40645c58bef81fca3036241.423 | HX1V03E 000000AT520281203345220170720 | HXH6734502146CX1947032500582088HCPP072573 | HXTJ157B 0032100120170711 |
Hope I am clear?
Regards,
Shweta
August 1, 2017 at 8:59 am
Ok, why not add an if statement to check the value of $line. If it's blank, don't update the value (so it'll retain its previous value from the "previous" line). So, for HX1, something like:
If it's your first line, it's going to be blank regardless, but not a lot you can do about that, as there's no "previous" record anyway.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 1, 2017 at 9:04 am
Thom A - Tuesday, August 1, 2017 8:59 AMOk, why not add an if statement to check the value of $line. If it's blank, don't update the value (so it'll retain its previous value from the "previous" line). So, for HX1, something like:{"HX1" {If ($line -ne "") {$HX1Line = $line}}If it's your first line, it's going to be blank regardless, but not a lot you can do about that, as there's no "previous" record anyway.
Hi Thom,
I don't want the power shell scripting. Power shell script is working successfully. My client wants this to be done in either using script component in SSIS or SQL. Can you please provide me with suggestions regarding the same?
Regards,
Shweta
August 1, 2017 at 9:20 am
SQL isn't going to work, not without some kind of column you can order by. You could, probably, set up a iterative process in SSIS to load the data, yes.
It would be much easier for someone to design something if they had a sample file to work with. Can you create a sample flat file? Also, what language would you want the component to be in? SSIS can be written in VB.net or C#. You'll need something you can debug and support yourself, rather than relying on Forum Users if you need any changes in the future. If you can't read or write either of these languages, then although someone could supply a solution, you'll be suppling a solution to your client that you can't support yourself (bad idea).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 1, 2017 at 9:26 am
Thom A - Tuesday, August 1, 2017 9:20 AMSQL isn't going to work, not without some kind of column you can order by. You could, probably, set up a iterative process in SSIS to load the data, yes.It would be much easier for someone to design something if they had a sample file to work with. Can you create a sample flat file? Also, what language would you want the component to be in? SSIS can be written in VB.net or C#. You'll need something you can debug and support yourself, rather than relying on Forum Users if you need any changes in the future. If you can't read or write either of these languages, then although someone could supply a solution, you'll be suppling a solution to your client that you can't support yourself (bad idea).
Hi Thom,
Yes I agree I am not comfortable with any scripting languages. I was hoping to get an idea to do in sql or SSIS. When you say iterative process in SSIS? can you please elaborate more on that.
Also find attached sample text file.
Regards,
Shweta
August 1, 2017 at 9:36 am
shwets47 - Tuesday, August 1, 2017 9:26 AMThom A - Tuesday, August 1, 2017 9:20 AMSQL isn't going to work, not without some kind of column you can order by. You could, probably, set up a iterative process in SSIS to load the data, yes.It would be much easier for someone to design something if they had a sample file to work with. Can you create a sample flat file? Also, what language would you want the component to be in? SSIS can be written in VB.net or C#. You'll need something you can debug and support yourself, rather than relying on Forum Users if you need any changes in the future. If you can't read or write either of these languages, then although someone could supply a solution, you'll be suppling a solution to your client that you can't support yourself (bad idea).
When you say iterative process in SSIS? can you please elaborate more on that.
Quoting from Google.
This process is frowned upon in SQL, as it's awful at iterative processes. Programming languages are normally better at it, but due to the nature of what you need to do here, you'd reap little benefit either (As the task can be run parallel anyway as you'd lose your row ordering).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 1, 2017 at 10:14 am
shwets47 - Tuesday, August 1, 2017 9:26 AMThom A - Tuesday, August 1, 2017 9:20 AMSQL isn't going to work, not without some kind of column you can order by. You could, probably, set up a iterative process in SSIS to load the data, yes.It would be much easier for someone to design something if they had a sample file to work with. Can you create a sample flat file? Also, what language would you want the component to be in? SSIS can be written in VB.net or C#. You'll need something you can debug and support yourself, rather than relying on Forum Users if you need any changes in the future. If you can't read or write either of these languages, then although someone could supply a solution, you'll be suppling a solution to your client that you can't support yourself (bad idea).
Hi Thom,
Yes I agree I am not comfortable with any scripting languages. I was hoping to get an idea to do in sql or SSIS. When you say iterative process in SSIS? can you please elaborate more on that.
Also find attached sample text file.Regards,
Shweta
Your text file bears little relationship to the test data you have posted previously, but the principle remains as Thom has explained it.
One way or another, you are going to have to process the rows in the order in which they appear in the file.
I cannot think of a way to do this in SQL Server without scripting, unfortunately. I can see two options:
(1)
a) Add a row number in your data flow, using a script component.
b) Write the entire data set out to a staging table
c) Use T-SQL to populate your missing columns, ordering by the row number you have created.
(2)
a) Use a script component to capture and assign values as they flow through the data pipeline, as Thom has already described.
Option (1) requires far less C# than option (2). Have a look here for a worked example.
Once you get to the point where you have the data in a staging table with a row number, we can help with the T-SQL to do the 'data-filling' you have described.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 2, 2017 at 9:42 am
Phil Parkin - Tuesday, August 1, 2017 10:14 AMshwets47 - Tuesday, August 1, 2017 9:26 AMThom A - Tuesday, August 1, 2017 9:20 AMSQL isn't going to work, not without some kind of column you can order by. You could, probably, set up a iterative process in SSIS to load the data, yes.It would be much easier for someone to design something if they had a sample file to work with. Can you create a sample flat file? Also, what language would you want the component to be in? SSIS can be written in VB.net or C#. You'll need something you can debug and support yourself, rather than relying on Forum Users if you need any changes in the future. If you can't read or write either of these languages, then although someone could supply a solution, you'll be suppling a solution to your client that you can't support yourself (bad idea).
Hi Thom,
Yes I agree I am not comfortable with any scripting languages. I was hoping to get an idea to do in sql or SSIS. When you say iterative process in SSIS? can you please elaborate more on that.
Also find attached sample text file.Regards,
ShwetaYour text file bears little relationship to the test data you have posted previously, but the principle remains as Thom has explained it.
One way or another, you are going to have to process the rows in the order in which they appear in the file.
I cannot think of a way to do this in SQL Server without scripting, unfortunately. I can see two options:
(1)
a) Add a row number in your data flow, using a script component.
b) Write the entire data set out to a staging table
c) Use T-SQL to populate your missing columns, ordering by the row number you have created.
(2)
a) Use a script component to capture and assign values as they flow through the data pipeline, as Thom has already described.
Option (1) requires far less C# than option (2). Have a look here for a worked example.
Once you get to the point where you have the data in a staging table with a row number, we can help with the T-SQL to do the 'data-filling' you have described.
Hi Phil,
I have created SSIS package with row number using Row_Number() function and now i have data looking as attached.
now I need to fill HX1V03E 00000070810311833348120170629 value in place of NULL.
I am stuck here. Can you guys give me a sql code to write the same?
Regards,
Shweta
August 2, 2017 at 9:46 am
A quick look at your first line, and [HX1Line] has a value of NULL. What should that contain, as there are no previously rows.
On the other hand, HXRLine is NULL the whole way down, so where is that value coming from?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 2, 2017 at 10:34 am
Thom A - Wednesday, August 2, 2017 9:46 AMA quick look at your first line, and [HX1Line] has a value of NULL. What should that contain, as there are no previously rows.On the other hand, HXRLine is NULL the whole way down, so where is that value coming from?
oops sorry had missed out an order by. please find attached new one. now the NULL in HX1Line should be populated with the above HX1Line value highlighted in bold.
You can ignore HXRline. there is no data coming for that.
Regards,
Shweta
August 2, 2017 at 2:17 pm
Here are two options: one using SUBSTRING, the other using STUFF. You may have to play around with the data lengths. You can also use strings instead of binary for the calculations. You may also want to add a PARTITION BY clause to the windowed MAX() function.
SELECT *,
CAST(SUBSTRING(MAX(CAST(FileRowID AS binary(6)) + CAST(HX1Line AS binary(128))) OVER(ORDER BY FileRowID ROWS UNBOUNDED PRECEDING), 7, 128) AS VARCHAR(128)),
CAST(STUFF(MAX(CAST(FileRowID AS binary(6)) + CAST(HX1Line AS binary(128))) OVER(ORDER BY FileRowID ROWS UNBOUNDED PRECEDING), 1, 6, NULL) AS VARCHAR(128))
FROM YourTable
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply