How to modify my script , When i need change my raw data file name,I don't need to edit QV script ?

[paulyeo11]

Hi All

I got the script from Staffan , May i know how to modify the below script , so that when i change the raw data file name , i can change it at excel file , with out open and edit QVW file.

  set vFile1 = '$(vRAWPath)Q_Payment_KL';

  SET vFile2 = '$(vRAWPath)Q_Payment_Penang';

  SET vFile3 = '$(vRAWPath)Q_Payment_TID';

Meaning the above script need to change. so that it will refer to XLS file for file name..  

Hope some one can share with me how to do it ?

Thank.

SET vDevelopment = 30;

IF $(vDevelopment) = 0 THEN

ELSEIF $(vDevelopment) = 30 THEN

  SET vRAWPath = 'C:\TEST\'; //local folder

//==============Hardcodedlist====================

  LET vNoOfFiles=3;

  set vFile1 = '$(vRAWPath)Q_Payment_KL';

  SET vFile2 = '$(vRAWPath)Q_Payment_Penang';

  SET vFile3 = '$(vRAWPath)Q_Payment_TID';

//============================================

  else

END IF

for i =1 to vNoOfFiles

For each vFileName in FileList('$(vRAWPath)*.txt')

if(subfield('$(vFileName)','.',1) = '$(vFile$(i))') then

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

endif

Next vFileName

next i

[stabben23]

What is the pathname where you want to keep Your excellist?

I'll help, because if you change path scriptline

if(subfield('$(vFileName)','.',1) = '$(vRAWPath)$(vFile)') then

need to be modified

[paulyeo11]

Hi Staffan

Right now RawDate folder equal C:\TEST\

For Excel file I want to save at folder C:\TEST\EXCEL

it is possible to make dynamic , because when I implement the project , I will save at Dropbox. Now for testing purpose pls use SET Path name , so I can change any time I like.

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +659:261804

[stabben23]

Hi Paul,

you should now be able to move Your files around and just rename the path, some scriptchanges is done.

SET vExcelPath = 'C:\TEST\EXCEL\';

SET vRAWPath = 'C:\TEST\';

[paulyeo11]

Hi Staffan

Thank you very much it work.

Next I need to recode SOURCE field. Now is using file name. Do you know how to recode in load script ? So that SOURCE field will display Q_Payment_KL TO KL etc.

Hope you can advise me.

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +659:261804

[stabben23]

If you mean that SOURCE should contain only the suffix like KL, HY, TPO, ofcource I can do that.

But one thing need to be known first, Is the name of the file always "Q_Payment_whatever"?

[paulyeo11]

Hi Staffan

Yes file name alway same. Never change.

For example Q_Payment_KL Alway same but recode to KL Or TKL allow to change.

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +659:261804

[stabben23]

In case you need the suffix after the second  '_' in Your filename, this is what you have to change in Your script:

subfield(FileBaseName(),'_',-1) as SOURCE

[stabben23]

Then subfield(FileBaseName(),'_',-1) as SOURCE is the answer

[paulyeo11]

I understand what you said , use subfield not allow me to change the name I want , as filename is fix , it will pick the first few character of file name and not flexible . I

prefer to use Mapping cannot because SOURCE field not created yet.

If no choice I will use the partial reload to recode the name with Mapping. So that in future I can change.

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +659:26180

[stabben23]

hmm, I dont really understand you now. can you elaborate a little bit more what you want to achieve here.