topic Load Data from Multiple CSV files in QlikView

Load Data from Multiple CSV files

anuradhaa — Wed, 25 Nov 2020 16:16:04 GMT

I'm Using below scrip to load data ,

[aaa_Temp]:

NoConcatenate

LOAD If(Len(Trim(Deadline)) = 0, Alt(Peek('Flag'), 1), RangeSum(Peek('Flag'), 1)) as Flag,

Id,

Title,

Labels

FROM

[aaa_*.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

[aaa]:

NoConcatenate

LOAD

Id,

Title,

Labels,

If(Len(Trim(Deadline)) = 0, Peek('Deadline'), Deadline) as Deadline

Resident aaa_Temp

Order By Flag desc, Deadline;

DROP Table aaa_Temp;

Here I have multiple CSV files as aaa_1,aa_2 etc.

So when i load data it create multiple aaa_temp table.

How can i load multiple files and create single aaa_temp table

Re: Load Data from Multiple CSV files

Chanty4u — Tue, 19 Apr 2016 08:18:26 GMT

did u chk disLoad only new data from multiple csv

Re: Loading different xls and CSV files

Re: Load Data from Multiple CSV files

sunny_talwar — Tue, 19 Apr 2016 08:19:06 GMT

Remove NoConcatenate to create just one table:

[aaa_Temp]:

NoConcatenate

LOAD If(Len(Trim(Deadline)) = 0, Alt(Peek('Flag'), 1), RangeSum(Peek('Flag'), 1)) as Flag,

Id,

Title,

Labels

FROM

[aaa_*.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

Re: Load Data from Multiple CSV files

anuradhaa — Tue, 19 Apr 2016 08:23:17 GMT

I have it to load multiple csvs like

[aaa_Temp]:

NoConcatenate

LOAD If(Len(Trim(Deadline)) = 0, Alt(Peek('Flag'), 1), RangeSum(Peek('Flag'), 1)) as Flag,

Id,

Title,

Labels

FROM

[aaa_*.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

[aaa]:

NoConcatenate

LOAD

Id,

Title,

Labels,

If(Len(Trim(Deadline)) = 0, Peek('Deadline'), Deadline) as Deadline

Resident aaa_Temp

Order By Flag desc, Deadline;

DROP Table aaa_Temp;

[bbb_Temp]:

NoConcatenate

LOAD If(Len(Trim(Deadline)) = 0, Alt(Peek('Flag'), 1), RangeSum(Peek('Flag'), 1)) as Flag,

Id,

Title,

Labels

FROM

[bbb_*.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

[bbb]:

NoConcatenate

LOAD

Id,

Title,

Labels,

If(Len(Trim(Deadline)) = 0, Peek('Deadline'), Deadline) as Deadline

Resident aaa_Temp

Order By Flag desc, Deadline;

DROP Table bbb_Temp;

Re: Load Data from Multiple CSV files

sunny_talwar — Tue, 19 Apr 2016 08:26:58 GMT

Try this:

[aaa_Temp]:

LOAD If(Len(Trim(Deadline)) = 0, Alt(Peek('Flag'), 1), RangeSum(Peek('Flag'), 1)) as Flag,

Id,

Title,

Labels

FROM

[aaa_*.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

[aaa]:

NoConcatenate

LOAD

Id,

Title,

Labels,

If(Len(Trim(Deadline)) = 0, Peek('Deadline'), Deadline) as Deadline

Resident aaa_Temp

Order By Flag desc, Deadline;

DROP Table aaa_Temp;

[bbb_Temp]:

LOAD If(Len(Trim(Deadline)) = 0, Alt(Peek('Flag'), 1), RangeSum(Peek('Flag'), 1)) as Flag,

Id,

Title,

Labels

FROM

[bbb_*.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

[bbb]:

NoConcatenate

LOAD

Id,

Title,

Labels,

If(Len(Trim(Deadline)) = 0, Peek('Deadline'), Deadline) as Deadline

Resident aaa_Temp

Order By Flag desc, Deadline;

DROP Table bbb_Temp;

or use Loop as mentioned here:

Loading Multiple Excel Sheets Dynamically along with file name and sheet name

Load multiple sheets and excel files

Re: Load Data from Multiple CSV files

swuehl — Tue, 19 Apr 2016 08:28:44 GMT

As Sunny said, the NOCONCATENATE is forcing QV to create separate tables.

Any reason why you are using it here? If you have a table create earlier with the exact same four fields, you can try

[aaa_Temp]:

LOAD If(Len(Trim(Deadline)) = 0, Alt(Peek('Flag'), 1), RangeSum(Peek('Flag'), 1)) as Flag,

Id,

Title,

Labels,

1 as aaa_Temp

FROM

[aaa_*.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

[aaa]:

NoConcatenate

LOAD

Id,

Title,

Labels,

If(Len(Trim(Deadline)) = 0, Peek('Deadline'), Deadline) as Deadline

Resident aaa_Temp

Order By Flag desc, Deadline;

DROP Table aaa_Temp;

i.e. remove the NOCONCATENATE and create a field that makes this table unique in field names.

Not really sure why you are not loading all *_Temp tables into one and then make a single final resident load though.