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

[paulyeo11]

What I mean is at GUI , when user click on SOURCE , I need the value label display not by file name or Subfield charater . I need it display like country.

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +659:261804

[paulyeo11]

Each filename is equal to one company or equal to one country.

Now I have 5 Coy :-

TDS

TID

TKL

TPG

TDT

Each I have have a file name for Payment raw data.

Q_Payment_KL = TKL

My Question is I like it recode to TKL or KL , depend on the box size available at GUI.

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +659:261804

[stabben23]

Ok, then create a inline table and Connect it to Your ARSummary table.With this

Countrys:

Load * inline[

%keycountry, Country

KL, countryname1

TKL, countryname2

];

subfield(FileBaseName(),'_',-1) as %keycountry

[stabben23]

I cant follow you right now, why do this in GUI? You need to show me what you want by using Pictures or in a table.

Right now you are saying that you want to recode Q_Payment_KL to TKL or? KL.

[paulyeo11]

‌yes ths should be correct approach .inline load is suitable. I will try it out . Many thank.

[paulyeo11]

Hi Staffan

I want to using mapping to recode terms  field thru excel file.

In the load data script i add below line :-

SET vINPUTPath = 'C:\TEST\EXCEL\'; //local folder

SET vFile1 = 'MapTerms.xlsx';

I create the excel MapTerms.xlsx excel file and enter the below text in excel cell :-

terms terms_new

1 30 Days

2 30 Days

3 30 Days

In my load script i add below :-

ApplyMap('MapTerms',@39:44T,'OTHERS') as [terms_n],

I write below script at Mapping Tab :-

MapTerms:

mapping LOAD

  [terms],

  [terms_new]

FROM  $(vINPUTPath)$(vFile1) (ooxml,embedded labels, table is [Terms]);

it does not create terms_n field.

Hope some one can help me.

For testing APPLY MAP !:-

Pls copy MapTerms.xls file to C:\TEST\EXCEL\

Paul Yeo

[stabben23]

Hi Paul,

you can't add the mapping table after ARSummary table, it need to be in the script before ARSummary ex in the filelist tab.

I can also se that you have mixed old and New Version in the code so it will not work as you expect.

[paulyeo11]

Hi Staffan

After i move the ApplyMap to before load script , now every thing working fine.

Yes i am very un-organise, messy. But now all work fine.

Paul

[stabben23]

maybe I was wrong, it looks like the script Works as expected, sorry. But move mapping tab before Main tab.

[stabben23]

Great Paul,

i'll give you some tips how to be more "unmessy"

First lesson, first hour on qlikview education:

• This is not SQL!
• Never script in main tab, use it to declare variables and path's
  
  I'll never foget thoose.