Filter during LOAD

Anonymous — Sun, 26 Oct 2014 21:33:23 GMT

Dear All

I have a problem, what I cannot solve.

I am loading data where in some cases two kind of data exists for the same day and same external_id, and I need only one ( the two types are CONTRACTED value and RECALCULATED value) . I would like to load only the recalculated value if the date and external ID are identical. How is it possible with load script?

Example:

EXT_ID	Date_valid_from	STATUS	Value
1	2014.08.01	CONTRACTED	5
1	2014.08.01	RECALCULATION	6
2	2014.08.01	CONTRACTED	10
2	2014.09.01	RECALCULATION	11
3	2014.09.01	CONTRACTED	4
3	2014.09.01	RECALCULATION	5

From the first two rows I would like to load the second one and drop the first one:

EXT_ID	Date_valid_from	STATUS	Value
1	2014.08.01	CONTRACTED	5
1	2014.08.01	RECALCULATION	6

In the 3rd and 4th row EXT_ID is the same , but Dates are different, so I need both rows:

EXT_ID	Date_valid_from	STATUS	Value
2	2014.08.01	CONTRACTED	10
2	2014.09.01	RECALCULATION	11

Row 5 and 6 is similar to the first one: I need only the RECALCULATION where EXT_ID and Date_valid_from are identical:

EXT_ID	Date_valid_from	STATUS	Value
3	2014.09.01	CONTRACTED	4
3	2014.09.01	RECALCULATION	5

So after LOAD I would require the following rows in the table:

EXT_ID	Date_valid_from	STATUS	Value
1	2014.08.01	RECALCULATION	6
2	2014.08.01	CONTRACTED	10
2	2014.09.01	RECALCULATION	11
3	2014.09.01	RECALCULATION	5

Could you help me with loading script?

Best regards and thanks

Zoltan

Re: Filter during LOAD

maxgro — Sun, 26 Oct 2014 21:52:51 GMT

SCRIPT

Directory;

tmp:

LOAD EXT_ID, Date_valid_from, STATUS, Value

FROM RECALC_LOAD.xlsx (ooxml, embedded labels, table is Munka1);

final:

NoConcatenate

load *

Resident tmp

where Peek(EXT_ID) <> EXT_ID or peek(Date_valid_from) <> Date_valid_from

order by EXT_ID, STATUS desc;

drop table tmp;

RESULT

Re: Filter during LOAD

Anonymous — Mon, 27 Oct 2014 07:40:25 GMT

Thank you Massimo, you solved my problem!

Zoltan

topic Re: Filter during LOAD in QlikView

Filter during LOAD

Re: Filter during LOAD

Re: Filter during LOAD