Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Gethyn
Creator
Creator

Joining data with conditions.

Hello,

I need some help with a script which loads data from a .qvd file. I then need to bring in data from and Excel spreadsheet and join it to the data in the .qvd file. There are two possible ways to join the data and if a particular line in the Excel file doesn't join by the first I would like it to join by the second.

I have tried left joining the excel file as shown in the script attached. But I only seem to get the data for the first Excel load and not the second. If I comment out the first, the second loads as it's supposed to.

Any help would be great.

Thanks,

Gethyn.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Nest those two applymap mappings:

date([Dispensed Date])&'|'&PurgeChar([Note Detail], 'IDid: ')&'|'&ApplyMap('LloydsMap', Rtrim(Product), ApplyMap('LloydsMapSM', rtrim(Product)) ) as lloydsJoin,

That way you can do the join with one load from the excel file.


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar

Nest those two applymap mappings:

date([Dispensed Date])&'|'&PurgeChar([Note Detail], 'IDid: ')&'|'&ApplyMap('LloydsMap', Rtrim(Product), ApplyMap('LloydsMapSM', rtrim(Product)) ) as lloydsJoin,

That way you can do the join with one load from the excel file.


talk is cheap, supply exceeds demand
Gethyn
Creator
Creator
Author

Hi Gysbert,

Thanks for you reply. What do I need to do in the section that loads the .qvd file that the Excel file joins to? I have the following two lines which represent the two ways that the spread sheet can join. The [Med ID] field will always exist in the .qvd but won't always have a match in the Excel (in the Excel file it could be populated but not be a match) in that case I want to join using the SIMPLE_GENERIC_C.

Date(ACTION_INSTANT)&'|'&MRN&'|'&[Med ID] as lloydsJoin,

Date(ACTION_INSTANT)&'|'&MRN&'|'&SIMPLE_GENERIC_C as lloydsJoinSM,

Thanks,

Gethyn.