Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Filter during LOAD

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_IDDate_valid_fromSTATUSValue
12014.08.01CONTRACTED5
12014.08.01RECALCULATION6
22014.08.01CONTRACTED10
22014.09.01RECALCULATION11
32014.09.01CONTRACTED4
32014.09.01RECALCULATION5

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

EXT_IDDate_valid_fromSTATUSValue
12014.08.01CONTRACTED5
12014.08.01RECALCULATION6

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

EXT_IDDate_valid_fromSTATUSValue
22014.08.01CONTRACTED10
22014.09.01RECALCULATION11

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_IDDate_valid_fromSTATUSValue
32014.09.01CONTRACTED4
32014.09.01RECALCULATION5

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

EXT_IDDate_valid_fromSTATUSValue
12014.08.01RECALCULATION6
22014.08.01CONTRACTED10
22014.09.01RECALCULATION11
32014.09.01RECALCULATION5

Could you help me with loading script?

Best regards and thanks

Zoltan

1 Solution

Accepted Solutions
maxgro
MVP
MVP


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

1.png

View solution in original post

2 Replies
maxgro
MVP
MVP


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

1.png

Anonymous
Not applicable
Author

Thank you Massimo, you solved my problem!

Zoltan