Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
subbareddykm
Creator II
Creator II

Dynamic

HI All,

I have a script like below,

Data:

LOAD [Col 1],

  [Col 2],

  [Col 3],

  [Col 4],

  [Col 5],

  [Col 6],

  [Col 7],

  'Data 001' as Flag

FROM (ooxml, embedded labels, table is Sheet1);

Concatenate

LOAD [Col 1],

  [Col 2],

  [Col 3],

  [Col 4],

  [Col 5],

  [Col 6],

  [Col 7],

  'Data 002' as Flag

FROM (ooxml, embedded labels, table is Sheet1);

Concatenate

LOAD [Col 1],

  [Col 2],

  [Col 3],

  [Col 4],

  [Col 5],

  [Col 6],

  [Col 7],

  'Data 003' as Flag

FROM (ooxml, embedded labels, table is Sheet1);

STORE Data into FinalData.Qvd(Qvd);

My requireemen is to automate my loading  is,

In my folder files are having like Data 001,Data 002,Data 003 and so on....

Now i am loading manually and creating flag with File name and storing into single file.

If i have 100 files in my folder i can't load them and create flag with file manually.

Can any one tell me how to write loops for this....?

THANKQ IN ADV

18 Replies
arvind_patil
Partner
Partner

Hi Subbareddy,

Try to use Load  *:



Data:


LOAD...

..

..

From

D:\Live\FolderName\*.qvd(qvd);

STORE Data into FinalData.Qvd(Qvd);

Thanks,

Arvind Patil

techvarun
Specialist II
Specialist II

Data:

LOAD [Col 1],

  [Col 2],

  [Col 3],

  [Col 4],

  [Col 5],

  [Col 6],

  [Col 7],

Left(FileName(),8) as Flag

FROM (ooxml, embedded labels, table is Sheet1);

Or

Data:

LOAD [Col 1],

  [Col 2],

  [Col 3],

  [Col 4],

  [Col 5],

  [Col 6],

  [Col 7],

Left(FileName(),8) as Flag

FROM (ooxml, embedded labels, table is Sheet1);

subbareddykm
Creator II
Creator II
Author

hi

it load all the files but what about my flag then?

Anonymous
Not applicable

have a look at the attachments...

LOAD
*,
FileName() as TYP
FROM
[Wert***.xlsx]
(
ooxmltable is Tabelle1);

its_anandrjs

Hi,

Try this ways for multiple excel loads and use FileBaseName function to create the table flag and use * for loading multiple excel files.

Data:

LOAD *

    FileBaseName() as TableFlag

FROM

[C:\Users\Home\Downloads\Data*.xlsx]

(ooxml, embedded labels, table is Sheet1);

STORE Data into FinalData.Qvd(Qvd);


Regards

Anand

PrashantSangle

Hi,

Instead of FileName(),

you can use filebasename() then you can avoid Left().

Regards,

Prashant

Great dreamer's dreams never fulfilled, they are always transcended.
subbareddykm
Creator II
Creator II
Author

HI, This working fine,

Can i apply same in my Data Base level (SQL DB)?

Anonymous
Not applicable

I see you should use * or ??? instead of *** 

arvind_patil
Partner
Partner

Hi Subbareddy,



Data:


LOAD...

..

..

From

D:\Live\FolderName\Data*.qvd(qvd);

STORE Data into FinalData.Qvd(Qvd);

Thanks,

Arvind Patil