Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

where to change the script , so it will load the file name i want QV to load ?

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.

1 Solution

Accepted Solutions
stabben23
Partner - Master
Partner - Master

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.

View solution in original post

11 Replies
stabben23
Partner - Master
Partner - Master

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.

rahulpawarb
Specialist III
Specialist III

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

paulyeo11
Master
Master
Author

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)

rahulpawarb
Specialist III
Specialist III

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

paulyeo11
Master
Master
Author

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)

rahulpawarb
Specialist III
Specialist III

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

paulyeo11
Master
Master
Author

Hi Sir

Enclosed the 2 raw data file and Code from HIC.

Paul

stabben23
Partner - Master
Partner - Master

You can use my code from above. just check the Attached qvw file.

paulyeo11
Master
Master
Author

Hi Sir

I have miss your reply . Thank you very much for your help,

Paul