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

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

paulyeo11 — Thu, 12 Jan 2017 11:22:19 GMT

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

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

paulyeo11 — Thu, 12 Jan 2017 11:22:57 GMT

my QVW

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

stabben23 — Thu, 12 Jan 2017 11:42:37 GMT

Hi Paul,

you need to self find a way to sort the files out, we cant help you with that.

Why do you want thoose 3 you have right now for ex? what is the unique with the files you want to load?

And you dont need to start a New thread for all "fix" in the script.

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

paulyeo11 — Thu, 12 Jan 2017 11:54:11 GMT

Hi Staffan

Thank you for teach me the correct approach. I will try myself.

As I want to develop the script and in case file name change , user can manage by themself. Also for develop stage I can change the file name with out touch the script directory section . As it will be very Long list of file , using excel is easy maintenance.

Okay I will not start New thread in future also.

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +659:261804

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

stabben23 — Thu, 12 Jan 2017 12:17:55 GMT

Hi Paul,

I can change the script so you can use an Excel list instead. You still need to add New filenames in excellist but not in script.

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

stabben23 — Thu, 12 Jan 2017 12:42:26 GMT

Here you go. Make sure that the fileName in Your Excel have the same name as they have from where you fetch them.

Q_Payment_TPO is just a dummy from my side.

Start to add all FileName in Excel then just run the script.

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

paulyeo11 — Thu, 12 Jan 2017 12:54:40 GMT

Hi Steffan

Wow you are very fast . Take you about 20 mins , For me i need to take may be 20 days.

As i ready don't know where to start. Appreciated very much.

Paul

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

stabben23 — Thu, 12 Jan 2017 12:59:58 GMT

Great Paul,

if you work as an consultant you will earn a lot of more Money than me, 20 days against 20 min

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

sunny_talwar — Thu, 12 Jan 2017 13:00:57 GMT

Hahahahah

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

paulyeo11 — Fri, 13 Jan 2017 07:17:36 GMT

Hi Staffan

Sorry for started new thread. ( Some people alway ask me start new thread , may be they want more point ).

After i add SET vInputPath = 'C:\TEST\EXCEL'; I get error msg unable to open my excel file.

I want to have diff folder to keep the excel file , as i don't want to mix raw data file and excel file.

The above link i have the detail info which i try but it does not work. Hope you can let me know where the issue ?

Once again thank you for your help.

Paul

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

stabben23 — Fri, 13 Jan 2017 07:22:28 GMT

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

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

paulyeo11 — Fri, 13 Jan 2017 07:35:02 GMT

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

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

stabben23 — Fri, 13 Jan 2017 08:03:24 GMT

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\';

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

paulyeo11 — Fri, 13 Jan 2017 08:52:26 GMT

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

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

stabben23 — Fri, 13 Jan 2017 09:10:18 GMT

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"?

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

paulyeo11 — Fri, 13 Jan 2017 09:14:02 GMT

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

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

stabben23 — Fri, 13 Jan 2017 09:14:20 GMT

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

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

stabben23 — Fri, 13 Jan 2017 09:21:24 GMT

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

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

paulyeo11 — Fri, 13 Jan 2017 09:22:26 GMT

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

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

stabben23 — Fri, 13 Jan 2017 09:28:24 GMT

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