Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Keep left from multiple files

Hi all,

I'm trying load complimentary data from multiple files keeping the records that exist on a previously loaded table:

Script

Transactions:

Load *;

select * from transactions;

Feedback:

LEFT KEEP (Transactions) LOAD transaction_id as trans_id,

    disposition

    FROM [lib://feedback files/*.txt]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Output

...

Feedback << pwmb_dispositions 06-24

Lines fetched: 238

Feedback-1 << pwmb_dispositions 06-26

Lines fetched: 133

Feedback-2 << pwmb_dispositions 06-27

Lines fetched: 116

Feedback-3 << pwmb_dispositions 06-28

Lines fetched: 145

Feedback-4 << pwmb_dispositions 06-29

Lines fetched: 30

...

$Syn 1 = trans_id+disposition

The problem is a synthetic key is generated. Some conclusions:

  1. If I remove the "LEFT KEEP (Transactions)" the data from the files are loaded fine (without the synthetic key) but not filtered.
  2. If I remove the "*.txt" and load a single file, the synthetic key is not generated and the data is filtered.


Is there any other way to achieve my goal?


Thanks

2 Replies
sunilkumarqv
Valued Contributor II

Re: Keep left from multiple files

If you want to filter data use exits functions end of the table like based but transaction_id should exists in Transactions

Transactions:

Load *,;

select * from transactions;

Feedback:

LOAD transaction_id as trans_id,

    disposition

    FROM [lib://feedback files/*.txt]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq) where exsits(transaction_id);

MVP
MVP

Re: Keep left from multiple files

The Keep option prevents the files from automatically concatenating and you cannot force concatenation with the concatenate keyword. You can load the files using

Where Exists(transaction_id)

in the load statement.