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: 
HarshChachara
Contributor
Contributor

Conditional Vlookup in load on Qliksense

Hi Community!

I have the following two tables

Main Table

Key - UniquePrimary Date
AAA01/24/2019
BBB04/19/2019

 

Second Table

Key - Non UniqueSecondary Date
AAA12/23/2018
AAA01/20/2019
BBB01/30/2019
AAA03/20/2019
BBB05/20/2019

 

In my load script I am trying to add another column onto Main Table that is informed by the Secondary Table in the following way :-

Displays '1' if the Key in the Secondary Table has a date within a certain range (e.g. in the same month) of the primary date of the Key in the Primary Table. Displays '0' otherwise.

In the above example, AAA would have a '1' as Secondary Date 01/20/2019 falls one week around Primary Date 01/24/2019. BBB would have  a '0'.

How would this be possible?

 

Labels (5)
13 Replies
sunny_talwar

One week in either direction or only one week before the Secondary Date?

HarshChachara
Contributor
Contributor
Author

Any direction in this example.


sunny_talwar

Try this

MainTable:
LOAD * INLINE [
    Key, Primary Date
    AAA, 01/24/2019
    BBB, 04/19/2019
];

tmpSecondTable:
LOAD * INLINE [
    Key, Secondary Date
    AAA, 12/23/2018
    AAA, 01/20/2019
    BBB, 01/30/2019
    AAA, 03/20/2019
    BBB, 05/20/2019
];

SecondTable:
LOAD Key,
	 Date([Secondary Date] - 7) as StartDate,
	 Date([Secondary Date] + 7) as EndDate,
	 If(Key = Peek('Key'), RangeSum(Peek('SNo'), 1), 1) as SNo
Resident tmpSecondTable
Order By Key, [Secondary Date];

DROP Table tmpSecondTable;

Left Join (MainTable)
IntervalMatch([Primary Date], Key)
LOAD StartDate,
	 EndDate,
	 Key
Resident SecondTable;

Left Join (MainTable)
LOAD *
Resident SecondTable;

DROP Table SecondTable;
lorenzoconforti
Specialist II
Specialist II

@sunny_talwar , I believe removing the synthetic keys might with the join might lead to potential issues of duplicated values. If there is more than one interval matching the Primary Date, the join will duplicate any value that might be associated to the keys (https://community.qlik.com/t5/Qlik-Design-Blog/IntervalMatch/ba-p/1464547)

I think a safer approach is to leave the synthetic keys with something like this; I've commented part of your code and added another matching interval to illustrate the potential problem:

MainTable:
LOAD * INLINE [
Key, Primary Date, Value
AAA, 24/01/2019, 5
BBB, 19/04/2019, 8
];

tmpSecondTable:
LOAD * INLINE [
Key, Secondary Date
AAA, 23/12/2018
AAA, 20/01/2019
AAA, 22/01/2019
BBB, 30/01/2019
AAA, 20/03/2019
BBB, 20/05/2019
];

SecondTable:
LOAD Key,
Date([Secondary Date] - 7) as StartDate,
Date([Secondary Date] + 7) as EndDate,
If(Key = Peek('Key'), RangeSum(Peek('SNo'), 1), 1) as SNo
Resident tmpSecondTable
Order By Key, [Secondary Date];

DROP Table tmpSecondTable;

MatchTable:
//Left Join (MainTable)
IntervalMatch([Primary Date], Key)
LOAD StartDate,
EndDate,
Key
Resident SecondTable;

//Left Join (MainTable)
//LOAD *
//Resident SecondTable;
//
//DROP Table SecondTable;

sunny_talwar

Running your script in QlikView... gave me this

image.png 

This doesn't look right...

And I think as long as Main Table has a Unique Key... I don't see any issue with joining....

sunny_talwar

Sample qvw attached

lorenzoconforti
Specialist II
Specialist II

Running your script (amended to match two periods, which is a realistic scenario) an extra record is inserted in the original table; you can see this in the screenshot below where I've sum by the value field. As you can see the total sum for AAA is 10, while in the original table was 5 (i.e. there is an extra row created by the join)

hisScript.png

If you leave the synthetic key then you don't have this issue since you are not joining thus not creating the extra record; the value for AAA is still 5 as it should be

myScript.png

---------------------------------------------------------------

For clarity, below your original script with the added Value field and extra date in the second table

MainTable:
LOAD * INLINE [
Key, Primary Date, Value
AAA, 24/01/2019, 5
BBB, 19/04/2019, 8
];

tmpSecondTable:
LOAD * INLINE [
Key, Secondary Date
AAA, 23/12/2018
AAA, 20/01/2019
AAA, 22/01/2019
BBB, 30/01/2019
AAA, 20/03/2019
BBB, 20/05/2019
];

SecondTable:
LOAD Key,
Date([Secondary Date] - 7) as StartDate,
Date([Secondary Date] + 7) as EndDate,
If(Key = Peek('Key'), RangeSum(Peek('SNo'), 1), 1) as SNo
Resident tmpSecondTable
Order By Key, [Secondary Date];

DROP Table tmpSecondTable;

Left Join (MainTable)
IntervalMatch([Primary Date], Key)
LOAD StartDate,
EndDate,
Key
Resident SecondTable;

Left Join (MainTable)
LOAD *
Resident SecondTable;

DROP Table SecondTable;

--------------------------------------

Below the amended script without the join

MainTable:
LOAD * INLINE [
Key, Primary Date, Value
AAA, 24/01/2019, 5
BBB, 19/04/2019, 8
];

tmpSecondTable:
LOAD * INLINE [
Key, Secondary Date
AAA, 23/12/2018
AAA, 20/01/2019
AAA, 22/01/2019
BBB, 30/01/2019
AAA, 20/03/2019
BBB, 20/05/2019
];

SecondTable:
LOAD Key,
Date([Secondary Date] - 7) as StartDate,
Date([Secondary Date] + 7) as EndDate,
If(Key = Peek('Key'), RangeSum(Peek('SNo'), 1), 1) as SNo
Resident tmpSecondTable
Order By Key, [Secondary Date];

DROP Table tmpSecondTable;

MatchTable:
IntervalMatch([Primary Date], Key)
LOAD StartDate,
EndDate,
Key
Resident SecondTable;

lorenzoconforti
Specialist II
Specialist II

I can't open your qvw at the moment

sunny_talwar

I see what you mean. I guess there are way around it, but to keep it simple, synthetic key might be a better way.