Loop for multiple file is showing synthetic table....

[qliklearnervir]

Hi,

I am loading data from files by using below script:

for i=$(vMin) to $(vMax)

for each File in filelist( vCSVFile & '\AS_BrochureRequest_$(i).csv')

Other:

LOAD *, Date(Date#(KEEPCHAR(FilePath, '0123456789'),'YYYYMMDD'),'DD/MM/YYYY') AS OtherDate,

         Right(FilePath,12) AS OtherFileName;

LOAD '$(File)'   AS FilePath,

  recno() AS Otherrecord,

  'Input File' AS DataSource1,

  'Brochure' AS Type1

FROM

'$(File)'

(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

Next File

Next i;

for i=$(vMin) to $(vMax)

for each File in filelist( vCSVFile & '\AS_GuestProfile_$(i).csv')

NoConcatenate

Other2:

LOAD *, Date(Date#(KEEPCHAR(FilePath, '0123456789'),'YYYYMMDD'),'DD/MM/YYYY') AS OtherDate,

         Right(FilePath,12) AS OtherFileName;

LOAD '$(File)'   AS FilePath,

  recno() AS Otherrecord,

  'Input File' AS DataSource1,

  'Recipient' AS Type1

FROM

'$(File)'

(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

Next File

Next i;

but as final outcome i am getting synthetic table for each file please suggest how to resolve this issue.

[senpradip007]

Try like below script:

for i=$(vMin) to $(vMax)

  for each File in filelist( vCSVFile & '\AS_BrochureRequest_$(i).csv')

Other:

LOAD 'Brochure Request' as Source,

         *, Date(Date#(KEEPCHAR(FilePath, '0123456789'),'YYYYMMDD'),'DD/MM/YYYY') AS OtherDate,

         Right(FilePath,12) AS OtherFileName;

LOAD '$(File)'   AS FilePath,

  recno() AS Otherrecord,

  'Input File' AS DataSource1,

  'Brochure' AS Type1

FROM

'$(File)'

(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

Next File 

Next i

for i=$(vMin) to $(vMax)

  for each File in filelist( vCSVFile & '\AS_GuestProfile_$(i).csv')

Other2:

LOAD 'Guest Profile' as Source,

          *, Date(Date#(KEEPCHAR(FilePath, '0123456789'),'YYYYMMDD'),'DD/MM/YYYY') AS OtherDate,

         Right(FilePath,12) AS OtherFileName;

LOAD '$(File)'   AS FilePath,

  recno() AS Otherrecord,

  'Input File' AS DataSource1,

  'Recipient' AS Type1

FROM

'$(File)'

(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

Next File

Next i

[PrashantSangle]

Hi,

Why are you using noconcatenate???

Your both table having same column??

Regards,

Prashant

[jonathandienst]

That's because you are loading the same field set into diferent tables, causing QV to create a composite key to associate the tables. You should load both sets of data into the same table, and then use a set expression with Type1 to select between the different sets.

I suggest you load the data like this:

Data:

LOAD '' as FilePath AutoGenerate 0;

For i= vMin to vMax

  Let vFile = '$(vCSVFile)\AS_BrochureRequest_$(i).csv';

  Concatenate(Data)

  LOAD *, Date(Date#(KEEPCHAR(FilePath, '0123456789'),'YYYYMMDD'),'DD/MM/YYYY') AS OtherDate,

  Right(FilePath,12) AS OtherFileName;

  LOAD '$(vFile)'   AS FilePath,

   recno() AS Otherrecord,

   'Input File' AS DataSource1,

   'Brochure' AS Type1

  FROM [$(vFile)]

  (txt, codepage is 1252, embedded labels, delimiter is '|', msq); 

  Let vFile = '$(vCSVFile)\AS_GuestProfile_$(i).csv';

  Concatenate(Data)

  LOAD *, Date(Date#(KEEPCHAR(FilePath, '0123456789'),'YYYYMMDD'),'DD/MM/YYYY') AS OtherDate,

  Right(FilePath,12) AS OtherFileName;

  LOAD '$(vFile)'   AS FilePath,

   recno() AS Otherrecord,

   'Input File' AS DataSource1,

   'Recipient' AS Type1  

  FROM [$(vFile)]

  (txt, codepage is 1252, embedded labels, delimiter is '|', msq);

Next i;