topic Re: Concatenate Dynamically in QlikView

Concatenate Dynamically

priyarane — Thu, 28 Jan 2016 03:59:53 GMT

Hi Community,

I have 2 qvds with date like QVD_30Jan2014 and QVD_31Jan2014 so we are loading them with '*' like QVD_*

But we are looking with out '*' and wanted to concatenate dynamically even if we have 2 or 4 like etc...

In future we may get more qvds with same name like QVD_30Jan2014 and QVD_31Jan2014 and QVD_01Mar2014 like that...

How to achieve it, can some one please help me.

Re: Concatenate Dynamically

sunny_talwar — Thu, 28 Jan 2016 04:01:30 GMT

Current structure should dynamically upload any file which has QVD_ at the beginning. I don't see what the issue is?

LOAD

FROM QVD_*.qvd (qvd);

Re: Concatenate Dynamically

amit_saini — Thu, 28 Jan 2016 04:08:53 GMT

Priya,

try:

for each file in filelist('D:\QVD\*')

LOAD

-----

FROM

[$(file)]

next file

Thanks,

Re: Concatenate Dynamically

HirisH_V7 — Thu, 28 Jan 2016 04:16:29 GMT

Hi,

your structure is fine and then the data with same file name means ,in that case same data for some records may be fetched ,

Then do this way,

LOAD

Distinct

QVD_*.qvd (qvd);

HTH,

Hirish

Re: Concatenate Dynamically

priyarane — Thu, 28 Jan 2016 04:32:08 GMT

Hi Sunny,

The issue is some times some of the columns are adding in new qvds

for Ex:

QVD_30Jan - 10 Columns

QVD_31Jan - 11 Columns in this scenario we are getting Synthetic keys, So we have planned to implement dynamic concatenate.

Re: Concatenate Dynamically

jagan — Thu, 28 Jan 2016 04:32:12 GMT

HI,

If all columns are same in all the QVDs then you can use like below

Data:

LOAD

FROM *.qvd (qvd);

If there are differences in columns between the files and still you want to concateante use below script

Data:

LOAD

'' AS Dummy

AUTOGENERATE (0);

for each file in filelist('Filepath:\QVD\*.qvd')

Concatenate(Data)

LOAD

FROM

[$(file)] (qvd);

next file

DROP FIELD Dummy;

Hope this helps you.

Regards,

jagan.

Re: Concatenate Dynamically

sunny_talwar — Thu, 28 Jan 2016 04:37:20 GMT

I think jagan‌ answered your question below

Re: Concatenate Dynamically

priyarane — Thu, 28 Jan 2016 04:53:31 GMT

Hi Jagan,

But I have different qvds in the same folder

Like CDM_TCD_30Jan.qvd, CDM_TCD_31Jan.qvd or CDM_BCD_30Jan

Re: Concatenate Dynamically

jagan — Thu, 28 Jan 2016 04:59:50 GMT

HI,

Then you define the pattern like begin with (CDM_TCD*.qvd), ends with(*BCD.qvd) or contains (*TCD*.qvd), whatever suits your requirement, similar to LIKE in SQL .

Data:

LOAD

FROM *.qvd (qvd);

Data:

LOAD

'' AS Dummy

AUTOGENERATE (0);

for each file in filelist('Filepath:\QVD\*.qvd')

Concatenate(Data)

LOAD

FROM

[$(file)] (qvd);

next file

DROP FIELD Dummy;

You need to change the highlighted portion based on your requirement.

Hope this helps you.

Regards,

Jagan.

Re: Concatenate Dynamically

priyarane — Thu, 28 Jan 2016 05:28:23 GMT

for different fields in qvds, Jagan's answer is working. Any way concatenating qvds is not answered yet, for this I will post seperately.