Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Here I have one logic that needs to be done as given in the below excel output. Can anyone help me out in this logic?
Output Table Should be
Try this
Table1:
LOAD * INLINE [
PN, CN, T1 Date, Cost
1, Ax, 30-08-2017, 2
1, Ax, 30-08-2017, 2
1, Bx, 30-08-2017, 2.5
1, Cx, 30-08-2017, 2.5
];
Table2:
LOAD * INLINE [
PN, CN, T2 Date, Value
1, Ax, 30-07-2017, 2
1, Ax, 26-08-2017, 2.25
1, Ax, 27-08-2017, 2.15
1, Ax, 30-08-2017, 2
1, Bx, 27-08-2017, 2.2
1, Bx, 28-08-2017, 2.35
1, Bx, 30-08-2017, 2.5
1, Cx, 25-08-2017, 2.5
1, Cx, 30-08-2017, 2.5
];
Left Join (Table1)
LOAD *,
Date(Start_T2_Date + IterNo() - 1) as [T1 Date]
While Start_T2_Date + IterNo() - 1 <= Start_T2_Date;
LOAD *,
Date(If(PN = Previous(PN) and CN = Previous(CN), Previous([T2 Date]), MakeDate(9999, 12, 31))) as Start_T2_Date,
Date([T2 Date] + 1) as End_T2_Date
Resident Table2
Order By PN, CN, [T2 Date] desc;
DROP Table Table2;
Try this
Table1:
LOAD * INLINE [
PN, CN, T1 Date, Cost
1, Ax, 30-08-2017, 2
1, Ax, 30-08-2017, 2
1, Bx, 30-08-2017, 2.5
1, Cx, 30-08-2017, 2.5
];
Table2:
LOAD * INLINE [
PN, CN, T2 Date, Value
1, Ax, 30-07-2017, 2
1, Ax, 26-08-2017, 2.25
1, Ax, 27-08-2017, 2.15
1, Ax, 30-08-2017, 2
1, Bx, 27-08-2017, 2.2
1, Bx, 28-08-2017, 2.35
1, Bx, 30-08-2017, 2.5
1, Cx, 25-08-2017, 2.5
1, Cx, 30-08-2017, 2.5
];
Left Join (Table1)
LOAD *,
Date(Start_T2_Date + IterNo() - 1) as [T1 Date]
While Start_T2_Date + IterNo() - 1 <= Start_T2_Date;
LOAD *,
Date(If(PN = Previous(PN) and CN = Previous(CN), Previous([T2 Date]), MakeDate(9999, 12, 31))) as Start_T2_Date,
Date([T2 Date] + 1) as End_T2_Date
Resident Table2
Order By PN, CN, [T2 Date] desc;
DROP Table Table2;
I tried your way. For me in Qlik Sense, Here find below screenshot
Hi,
do You have in Script
SET DateFormat='DD-MM-YYYY';
antoniotiman is right, it seems that your date field may not be read as date... look here
It's working now, Thanks Sunny.
Great
Here I am including SD in Table 1 and PL in Table 2, if SD for PN = 1, then show PN only for PL=1 in Table 2, if SD for PN = 2, then show PN only for PL=2 in Table 2. I tried loading seperately for SD=1 and SD =2 and Concatenated both using where not exist condition. But it doesn't work....
Table1:
LOAD * INLINE [
PN, CN, T1 Date, Cost, SD
1, Ax, 30-08-2017, 2,1
1, Ax, 30-08-2017, 2,2
1, Bx, 30-08-2017, 2.5,1
1, Cx, 30-08-2017, 2.5,1
];
Table2:
LOAD * INLINE [
PN, CN, T2 Date, Value, PL
1, Ax, 30-07-2017, 2,1
1, Ax, 26-08-2017, 2.25,2
1, Ax, 27-08-2017, 2.15,1
1, Ax, 30-08-2017, 2,1
1, Bx, 27-08-2017, 2.2,2
1, Bx, 28-08-2017, 2.35,1
1, Bx, 30-08-2017, 2.5,1
1, Cx, 25-08-2017, 2.5,1
1, Cx, 30-08-2017, 2.5,1
];
Not sure I understand.. what is the expected output here?
Table1:
LOAD * INLINE [
PN, CN, T1 Date, Cost, SD
1, Ax, 30-08-2017, 2,1
1, Ax, 30-08-2017, 2,2
1, Bx, 30-08-2017, 2.5,2
1, Cx, 30-08-2017, 2.5,2
];
Table2:
LOAD * INLINE [
PN, CN, T2 Date, Value, PL
1, Ax, 30-07-2017, 2,1
1, Ax, 26-08-2017, 2.25,2
1, Ax, 27-08-2017, 2.15,1
1, Ax, 30-08-2017, 2,1
1, Bx, 27-08-2017, 2.2,2
1, Bx, 28-08-2017, 2.35,1
1, Bx, 30-08-2017, 2.5,2
1, Cx, 25-08-2017, 2.5,1
1, Cx, 30-08-2017, 2.5,2
];
Output :