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]

I'll think you are going in the wrong direction. This will become a messy datamodel,

You can not drop AR table. This is very much needed.

You can't Connect Your tables With SOURCE, they have nothing common except the Fieldname.

You have to know how to present Your data in front end before going any further.

If you dont concatenate AR and SALES, how will you Connect Your Dimensionstable like customername and dates and whatever?

You are not doing any partial reload, you do a resident load which is not the same.

I'm really sorry to say that I think this is a little bit to complicated for you and also to overview over the community.

You will need a qlikviewconsultant beside right now. This is not a just a simple question/discussion in the community it a Complete "develop my model".

When we solve one problem, you will get next and next and next. because you cant see whats happens in the next step and neither do I, and thats what we have to know.

I dont know how I can help during my ordinary work, which right now is Not 100% qlikview consultant.

One way is to buy me a ticket to singapore and I will come to Your Place and help

Just to puch you into the right direction. Concatenate the transaction tables SALES and AR, create a flag in both tables like 'Sales' as Table in SALES and 'AR' as Table in AR. Then you have a Field which can be used to separate them from each other.

Keep all common Dimension Fields With the same name, like cust_id, grouping, or anything else witch can be used as Dimension. You can futher on use set analysis to aggregate values from the tables like sum({<Table={'Sales'}>}cost) to get the salesvalue and sum({<Table={'AR'}>}cost) to get the arvalue. Look in the Attached qvwfile.

Keep up the good work and good Luck.

[paulyeo11]

Hi staffen

Thank you very much for your sharing.

1) You are right i cannot any how drop AR Table , AS after drop AR table the whole missing.

2) Now i agree with you , with out noconcatenate before load AR Table , i am able to see great benefit. the data modek look great and more organise.

3) You are right i am not doing partial reload , i am doing resident load. ( I will google to find out the different ).

4) After i have keep both table field name same for cust_id and company , i see the a lot of  benefit i can use one field name for company.

5) I like the way you using 'AR' as table to filter AR Table, which is some thing new to me.

6) You also mentioned i cannot link using SOURCE field, now AR and Sales Table i link thru below script , it work fine,

      autonumber( @1:10T & '_' & 'FileBaseName' ) as ARKey,    

Enclosed my latest QVW.

Paul