topic Re: Keep left from multiple files in App Development

Keep left from multiple files

Wed, 01 Jul 2015 22:48:06 GMT

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:

If I remove the "LEFT KEEP (Transactions)" the data from the files are loaded fine (without the synthetic key) but not filtered.
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

Re: Keep left from multiple files

sunilkumarqv — Thu, 02 Jul 2015 06:08:39 GMT

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

Re: Keep left from multiple files

jonathandienst — Thu, 02 Jul 2015 06:23:55 GMT

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.