Concatenate Dynamically

[priyarane]

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.

[sunny_talwar]

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

[amit_saini]

Priya,

try:

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

LOAD

    -----

FROM

[$(file)]

next file

Thanks,

AS

[HirisH_V7]

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

[priyarane]

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.

[jagan]

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.

[sunny_talwar]

I think jagan‌ answered your question below

[priyarane]

Hi Jagan,

But I have different qvds in the same folder

Like CDM_TCD_30Jan.qvd, CDM_TCD_31Jan.qvd or CDM_BCD_30Jan

[jagan · Accepted solution]

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.

[priyarane]

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