Get dynamically a flag from a different date

[parayu]

Hello,

I have this scenario:

Table:
LOAD * INLINE [
Date, Customer, ArrangementID, Exposure, Flag,
2018-07-30, Alex, 11, 90, Y,
2018-07-30, Jack, 21, 200, N,
2018-08-31, Alex, 11, 100, N,
2018-08-31, Alex, 12, 500, N,
2018-08-31, Jack, 21, 220, N,
2018-09-30, Alex, 11, 300, N,
2018-09-30, Jack, 21, 250, Y,
];

I want to select first Date (2018-07-30),  and then I want to create a calculated dimension or measure that will get me on the same line, the Flag from a future date (2018-09-30) . I want to hardcode the date(in the expression) for now, but in the future I will use an imput box and a variable for that Date.

I expect that my result would be like this:

Date, Customer, ArrangementID, Exposure, Flag, CalculatedFlag
2018-07-30, Alex, 11, 90, Y,   N
2018-07-30, Jack, 21, 200, N, Y 

Can you please tell me if this is possible in Qlik Sense?

Thank you,

Bogdan

[agni_gold]

can you please explain your requirement bit clear ?

Does not understand what you want to do.

[jensmunnichs]

Hey,

So I think what you're saying is that you want to look up the Flag of the ArrangmentID in the current row but for a different date, in this case for 2018-09-30. This probably isn't the most optimal solution, but I think it works, PFA.

So because you're essentially wanting to look up a value based on 2 fields (ArrangementID and Date), I had to create 2 combined fields, one for the value to look up, and one to look up the value in*. I created 2 different LookupV's, one for your hardcoded date, and one as an example of how you can do this using a dynamic date (in this case by adding 2 months to the date in the current row). Make sure to only uncomment one at a time.

I hope this is what you're looking for, if not let me know 🙂

*LookupV can probably be calculated in the Lookup function instead, but this way I was able to check the value it was trying to look up. Performance wise it might be better to move the definition for LookupV to the Lookup function, but I'm not sure

[parayu]

I can't open your file because I work with Sense, and with QlikView, I already opend 3 times different apps so I was blocked. In the next days I will receive a qlik license from a friend, I will take a look on your file and come back to you.

THanks

[jensmunnichs]

Right, my mistake, here is the script in case you don't want to wait. It's currently set to dynamically look at the current date + 2 months:
(p.s. I also just realised I called the final table Temp2, should probably change that to 'final' or something)

Table:
LOAD * INLINE [
Date, Customer, ArrangementID, Exposure, Flag,
2018-07-30, Alex, 11, 90, Y,
2018-07-30, Jack, 21, 200, N,
2018-08-31, Alex, 11, 100, N,
2018-08-31, Alex, 12, 500, N,
2018-08-31, Jack, 21, 220, N,
2018-09-30, Alex, 11, 300, N,
2018-09-30, Jack, 21, 250, Y,
];

Temp:
LOAD *,
Date(Date#(Date, 'YYYY-MM-DD'), 'YYYY-MM-DD') & ArrangementID as Combo,
// '2018-09-30' & ' ' & ArrangementID as LookupV //Uncomment for hardcoded date
Date(AddMonths(Date, 2), 'YYYY-MM-DD') & ArrangementID as LookupV //Uncomment for dynamic date
Resident Table;

Drop table Table;

Temp2:
LOAD *,
Lookup('Flag', 'Combo', LookupV, 'Temp') as Flag2
Resident Temp;

Drop table Temp;
Drop Field Combo;
Drop Field LookupV;