topic Re: Generate table with the last value in QlikView

Generate table with the last value

Fri, 04 Dec 2015 17:07:40 GMT

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.

Re: Generate table with the last value

sunny_talwar — Fri, 04 Dec 2015 17:34:26 GMT

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;

Re: Generate table with the last value

Wed, 09 Dec 2015 14:39:05 GMT

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 :