Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Hi,
Why are you using noconcatenate???
Your both table having same column??
Regards,
Prashant
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;