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]

Hi staffan ,

I just re-org my sample script.

So i make main for declare path and for SET LET etc.

Thank you for your tips. 1) not quite understand what you mean it is not SQL ? for point 2 main section are you refer to the enclosed qvw main Tab ?

[stabben23]

Looks Nice Paul,

This is not SQL means that qlikview is so much more than just sql queries (cubes).

Main tab "should" be clean from scriptcode, always start to add a New tab when scripting, then declare variables if needed in main. This is not a rule ofcource.

[paulyeo11]

Hi Staffen

Now I want to try load 2 table. SALES and AR Table.

I  manage to modify your script to load the AR table or SALES table individually.

But when i try to load both SALES & AR table at the same time . My issue is after loading SALES Table, It refuse to load the AR Table.

Can you pls help me make it allow load more then 1 table ?

For testing :-

Copy the 2 excel file to C:\TEST\EXCEL

Copy the 2 sales Raw data data to C:\TEST

Enclosed also my QVW file.

Paul

[stabben23]

Hi Paul,

you need to drop the table FileList in SALES tab. Because you can not create 2 table With the same name.

So when you try to Load FileList in AR tab the name of the table will be Filelist -1. So Your peek an noofrows will not find the table filelist.

So just add drop Table FileList; at the end of SALES tab script.

[paulyeo11]

Hi Staffan

Thank you very much for your help. it work after i add the the below at sales Tab :-

drop Table FileList;

But it combine the 2 table into one. May i request them these 2 table not to combine into one ?

Because in my actual AR table and Sales Table , they are different.  As AR table only have 6 column on their AR Aging column 30 , 60 , 90 , 120 , 150 day. which is very different with sales table.  As my sales order table it is more complicated. it have many field like segment , Brand , SBU , product code etc.

Also beside i adding AR and sales Table , i still need to add many other table like GL table , inventory table , and i cannot combine all table into one table , it will be very hard for me to manage and the file will be very big.

Hope you can share with me , how to save the sales table into SALES table name before drop. and go to load the AR table and save the table name to AR. So i will have 2 table after load.

Paul Yeo

[stabben23]

If you dont want to concatenate them, which they will be now (automatically) becauce they have the same fieldnames inside. They also have the same tablename AR.

If You want to separate them, change the name of the tables like SALES for sales table and AR for AR table.

This will end up will a lot of synthetic key so you need to change the fieldnames inside the tables.

Start With that and come back.

[stabben23]

You could also save them into qvd's if you want to use them later in other Project.

[paulyeo11]

Hi Staffen

I read and understand that , you told to change the Table to name ,  When my table name below script , the table name will be SALES. ( 1 Table ), it does not create 2 Table.

Sales Tab

SALES:

LOAD left(FileBaseName(), 4) AS Report5_pay,

AR Tab

AR:

LOAD left(FileBaseName(), 4) AS Report5_pay,

Paul Yeo

[stabben23]

They will still be concatenated because of the fieldnames in the tables. You have to change fieldnames or use noconcatenate berfore you load the AR table. if you use noconcatenate it will create  synthetic key on all Fields.

[paulyeo11]

Hi Staffen

After i add the  noconcatenate before AR: Table load , and i add drop Table AR; Now my table model become very neat. Now look like it work perfect.

Hi staffen

Now i like to ask you , how to recode grouping using partial reload for filed name GROUP_CLASS_ ?

When i load 2 table , my partial reload become not working fine. As you can notice that row 3 become balnk.

Enclosed the 2 QVW ,

1) load i only one singapore raw data no issue with partial reload,

2)  QVW i load with singapore and malaysia raw data. it have issue on table 1 row 3 and 4 missing value on GROUP_CLASS_ field.

Paul