Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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.
yes ths should be correct approach .inline load is suitable. I will try it out . Many thank.
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
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.
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
maybe I was wrong, it looks like the script Works as expected, sorry. But move mapping tab before Main tab.
Great Paul,
i'll give you some tips how to be more "unmessy"
First lesson, first hour on qlikview education: