Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Gethyn
Not applicable

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
Not applicable

Re: Joining data with conditions.

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
2 Replies
Gysbert_Wassenaar
Not applicable

Re: Joining data with conditions.

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
Not applicable

Re: Joining data with conditions.

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.