Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
My below script work fine , May i know how to make it load only below 3 file ?
vFile1
vFile2
vFile3
Now it load all row data at C:\TEST , May i know how to modify the script ?
SET vDevelopment = 30;
IF $(vDevelopment) = 0 THEN
ELSEIF $(vDevelopment) = 30 THEN
SET vRAWPath = 'C:\TEST\'; //local folder
set vFile1 = 'Q_Payment_KL';
SET vFile2 = 'Q_Payment_Penang';
SET vFile3 = 'Q_Payment_TID';
else
END IF
For each vFileName in FileList('$(vRAWPath)\*.txt')
ARSummary:
LOAD left(FileBaseName(), 4) AS Report5_pay,
FileBaseName() as SOURCE,
@1:9T as [cust_id],
@10:38T as [company_pay],
if(right(@81:99T,1)='-', '-' & left(@81:99T, len(@81:99T)-1),@81:99T) as curren
FROM "$(vFileName)" (ansi, fix, no labels, header is 0, record is line);
Next vFileName
Remark :- Pls unzip the file to C:\TEST for testing.
Hi Paul,
I have change a little bit in Your code, this will be a little bit hardcoded when you have the files in a list.
Have a look and se what you think.
Hi Paul,
I have change a little bit in Your code, this will be a little bit hardcoded when you have the files in a list.
Have a look and se what you think.
Hello Paul,
Hope you are doing well. Please use below approach to load the given files.
FileList:
LOAD * INLINE [
FileName
Q_Payment_KL.txt
Q_Payment_Penang.txt
Q_Payment_TID.txt
];
FOR i=0 TO NoOfRows('FileList')-1
LET vFile = PEEK('FileName', $(i), 'FileList');
ARSummary:
LOAD Left(FileBaseName(),4) AS Report5_Pay,
FileBaseName() as SOURCE,
@1:9T as [cust_id],
@10:38T as [company_pay],
if(right(@81:99T,1)='-','-' & left(@81:99T, len(@81:99T)-1,@81:99T) as curren
FROM $(vFile) (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
NEXT
Let me know does this solves your problem.
Regards!
Rahul
Hi sir
Thank you for asking me and sharing with me , i got error code :-
Error in expression:
Left takes 2 parameters
ARSummary:
LOAD Left(FileBaseName(),4) AS Report5_Pay,
FileBaseName() as SOURCE,
@1:9T as [cust_id],
@10:38T as [company_pay],
if(right(@81:99T,1)='-','-' & left(@81:99T, len(@81:99T)-1,@81:99T) as curren
FROM Q_Payment_KL.txt (txt, codepage is 1252, embedded labels, delimiter is ',', msq)
Hello Paul,
Extremely sorry for typo. Please replace the definition of curren field with following:
if(right(@81:99T,1)='-','-' & left(@81:99T, len(@81:99T)-1),@81:99T) as curren
Hope this will help.
Thank you!
Rahul
Hi Sir
it is okay , i still get the error :-
Field not found - <@1:9T>
ARSummary:
LOAD Left(FileBaseName(),4) AS Report5_Pay,
FileBaseName() as SOURCE,
@1:9T as [cust_id],
@10:38T as [company_pay]
FROM Q_Payment_KL.txt (txt, codepage is 1252, embedded labels, delimiter is ',', msq)
Hello Paul,
Please replace the load statement for ARSummary table given by me with your original load statement. Once done then change the from clause as below.
FROM $(vFile) (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
If you still have this problem then share txt files which you are using.
Thank you!
Rahul
Hi Sir
Enclosed the 2 raw data file and Code from HIC.
Paul
You can use my code from above. just check the Attached qvw file.
Hi Sir
I have miss your reply . Thank you very much for your help,
Paul