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: 
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
Specialist II
Specialist II

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);

jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein