topic Re: Dynamic in QlikView

Dynamic

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

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

Re: Dynamic

arvind_patil — Mon, 14 Aug 2017 08:50:30 GMT

Hi Subbareddy,

Try to use Load *:

Data:

LOAD...

From

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

STORE Data into FinalData.Qvd(Qvd);

Thanks,

Arvind Patil

Re: Dynamic

techvarun — Mon, 14 Aug 2017 08:55:17 GMT

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

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

Re: Dynamic

subbareddykm — Mon, 14 Aug 2017 08:57:09 GMT

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

Re: Dynamic

Anonymous — Mon, 14 Aug 2017 08:58:32 GMT

have a look at the attachments...

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

Re: Dynamic

its_anandrjs — Mon, 14 Aug 2017 08:59:12 GMT

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

Re: Dynamic

PrashantSangle — Mon, 14 Aug 2017 09:00:33 GMT

Hi,

Instead of FileName(),

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

Regards,

Prashant

Re: Dynamic

subbareddykm — Mon, 14 Aug 2017 09:02:06 GMT

HI, This working fine,

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

Re: Dynamic

Anonymous — Mon, 14 Aug 2017 09:02:08 GMT

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

Re: Dynamic

arvind_patil — Mon, 14 Aug 2017 09:02:09 GMT

Hi Subbareddy,

Data:

LOAD...

From

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

STORE Data into FinalData.Qvd(Qvd);

Thanks,

Arvind Patil

Re: Dynamic

subbareddykm — Mon, 14 Aug 2017 09:57:10 GMT

Data :

LOAD *,

FILENAME() as Flag;

SELECT *

FROM [QVDB].[dbo].[Data_*];

STORE Data into Data.Qvd(Qvd);

Actual My DB Table name will look like Data_001,Data_002 and so on.

My above script is not working..

Can anyone suggest how to do it ?

Re: Dynamic

avinashelite — Mon, 14 Aug 2017 10:01:31 GMT

This works fine with flat files not with the DB!! you need write multiple load statements or stored proc for this

Re: Dynamic

PrashantSangle — Mon, 14 Aug 2017 10:03:20 GMT

Hi,

Can you post the error which you are getting??

Regards,

Re: Dynamic

its_anandrjs — Mon, 14 Aug 2017 10:21:36 GMT

If you have DB tables then use the Loop here for loading multiple loads from SQL DB.

Regards

Anand

Re: Dynamic

Anonymous — Mon, 14 Aug 2017 10:26:17 GMT

you could try something like that, but the question is, where you get your max table number from:

for i=1 to 100

Data :

LOAD *,$(i) as Flag;

SQL

SELECT * FROM [QVDB].[dbo].[Data_$(i)];

Re: Dynamic

subbareddykm — Wed, 16 Aug 2017 05:24:12 GMT

for i=1 to 100

Data :

LOAD *,$(i) as Flag;

SQL

SELECT * FROM [QVDB].[dbo].[Data_$(i)];

Above code will execute 1 t0 100,

But If want to by user selection how to write a code for it...

Let say my user will say 4,17,31,65,89 etc to load.... will loop will work here ?

Please suggest some inputs here

Re: Dynamic

techvarun — Wed, 16 Aug 2017 05:32:30 GMT

In this case, You have to load the entire data from DB and need to apply the filter in Where Clause or in front end.

Re: Dynamic

subbareddykm — Wed, 16 Aug 2017 05:54:48 GMT

Is there any other solution?

Re: Dynamic

Anonymous — Wed, 16 Aug 2017 06:09:40 GMT

we talk about users @ QlikView SERVER?