BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
TurnTheBacon
Fluorite | Level 6

Working in an SQL Join transformation in DI Studio, I need an expression which says that "All fields in this column should have the same value as the field where Current=1".

Only the rows with Current=1 has "disability_flag" filled out, and I need to copy it to those with Current=0.

In example, this input table:

PKDisability_FlagCurrent
10
111
20
20
201

Should become this output table:

PKDisability_FlagCurrent
110
111
200
200
201

For some reason I can't think of what expression to use. I'd appreciate any advice, thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20

Join the table onto itself (aliases lets say a and b), on PK.

mapping:

b.disability_flg

Where:

b.current = '1'

Data never sleeps

View solution in original post

4 REPLIES 4
LinusH
Tourmaline | Level 20

Join the table onto itself (aliases lets say a and b), on PK.

mapping:

b.disability_flg

Where:

b.current = '1'

Data never sleeps
TurnTheBacon
Fluorite | Level 6

Thanks Linus. I know how to join tables to themselves if I can drag-and-drop them from the folders menu into the SQL Join "Create" window, but in this case the input table is a previous SQL Join. How can I make a copy of it in the DI Studio SQL Join Create window?

Skjermbilde.PNG

I had hoped to resolve the problem another way. So far I've been grouping by the PK and used expressions to select for example the min(ValidFrom_DTTM) and max(ValidTo_DTTM) to combine 2-3 rows into one. My aim is that the combined row should have the most updated/current disability_flag value.

LinusH
Tourmaline | Level 20

Just map it twice in the job diagram, the second mapping prompts you for specifying an alias.

Data never sleeps
TurnTheBacon
Fluorite | Level 6

Perfect, now all the other pieces fell into place as well. Thank you. Smiley Happy

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 861 views
  • 3 likes
  • 2 in conversation