Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I want to generate table by getting the last value of a field.
for supplier S2 on table 1
For 2015_Q1, I have a status in table 2, so I get this status.
For 2015_Q2 I don't have a status in table 2, so I get the last status (2015_Q1 status)
I have table 1 and Table 2, and I want to obtain table 3.
Table 1
Quarter | supplier | rank |
2015_Q1 | S1 | rank1 |
2015_Q1 | S2 | rank2 |
2015_Q2 | S1 | rank3 |
2015_Q2 | S2 | rank4 |
2015_Q2 | S3 | rank5 |
2015_Q2 | S4 | rank6 |
2015_Q3 | S1 | rank7 |
2015_Q3 | S2 | rank8 |
2015_Q3 | S3 | rank9 |
2015_Q3 | S4 | rank10 |
2015_Q3 | S5 | rank11 |
Table 2:
Quarter | Supplier | status |
2015_Q1 | S1 | status1 |
2015_Q1 | S2 | status1 |
2015_Q2 | S1 | status2 |
2015_Q2 | S3 | status2 |
2015_Q2 | S4 | status1 |
2015_Q2 | S5 | status1 |
2015_Q3 | S1 | status2 |
2015_Q3 | S2 | status3 |
2015_Q3 | S5 | status1 |
2015_Q4 | S5 | status1 |
table 3 :
Quarter | supplier | rank | status |
2015_Q1 | S1 | rank1 | status1 |
2015_Q1 | S2 | rank2 | status1 |
2015_Q2 | S1 | rank3 | status2 |
2015_Q2 | S2 | rank4 | status1 |
2015_Q2 | S3 | rank5 | status2 |
2015_Q2 | S4 | rank6 | status1 |
2015_Q3 | S1 | rank7 | status2 |
2015_Q3 | S2 | rank8 | status3 |
2015_Q3 | S3 | rank9 | status2 |
2015_Q3 | S4 | rank10 | status1 |
2015_Q3 | S5 | rank11 | status1 |
thank's for help.
Try this:
Table:
LOAD Quarter,
supplier as Supplier,
rank
FROM
[https://community.qlik.com/thread/194197]
(html, codepage is 1252, embedded labels, table is @1);
Join(Table)
LOAD Quarter,
Supplier,
status
FROM
[https://community.qlik.com/thread/194197]
(html, codepage is 1252, embedded labels, table is @2);
Temp:
NoConcatenate
LOAD *
Resident Table
Where Len(Trim(rank)) > 0;
DROP Table Table;
Join (Temp)
LOAD Supplier,
FirstValue(status) as NewStatus
Resident Temp
Group By Supplier;
FinalTable:
LOAD Quarter,
Supplier,
rank,
If(Len(Trim(status)) = 0, NewStatus, status) as status
Resident Temp;
DROP Table Temp;
hello,
thank you for your help.
I test your solution with another example but it doesn't work well.
if rank is empty, the row is deleted.
for all supplier, I have the first supplier status and not the last status.
Table 1
Quarter | supplier | rank |
2015_Q1 | S1 | rank1 |
2015_Q1 | S2 | |
2015_Q2 | S1 | rank3 |
2015_Q2 | S2 | rank4 |
2015_Q2 | S3 | rank5 |
2015_Q2 | S4 | rank6 |
2015_Q3 | S1 | rank7 |
2015_Q3 | S2 | rank8 |
2015_Q3 | S3 | rank9 |
2015_Q3 | S4 | rank10 |
2015_Q3 | S5 | rank11 |
Table 2 :
Quarter | Supplier | status |
2015_Q1 | S1 | status1 |
2015_Q1 | S2 | status1 |
2015_Q2 | S1 | status2 |
2015_Q2 | S3 | status2 |
2015_Q2 | S4 | status1 |
2015_Q2 | S5 | status1 |
2015_Q3 | S2 | status3 |
2015_Q3 | S5 | status1 |
2015_Q4 | S5 | status1 |
expected result :
Quarter | supplier | rank | status |
2015_Q1 | S1 | rank1 | status1 |
2015_Q1 | S2 | status1 | |
2015_Q2 | S1 | rank3 | status2 |
2015_Q2 | S2 | rank4 | status1 |
2015_Q2 | S3 | rank5 | status2 |
2015_Q2 | S4 | rank6 | status1 |
2015_Q3 | S1 | rank7 | status2 |
2015_Q3 | S2 | rank8 | status3 |
2015_Q3 | S3 | rank9 | status2 |
2015_Q3 | S4 | rank10 | status1 |
2015_Q3 | S5 | rank11 | status1 |
result with application :