topic Re: QVD Logic in QlikView

script

Anonymous — Mon, 08 Feb 2016 04:45:46 GMT

Hello

Script logic

please help me in this regard.

Re: QVD Logic

Anonymous — Mon, 08 Feb 2016 04:56:00 GMT

any suggestions?

Re: QVD Logic

tamilarasu — Mon, 08 Feb 2016 05:01:59 GMT

Hi John,

Check the below link. You might get some idea.

Loading from multiple Excel files and multiple sheets

Re: QVD Logic

manojkulkarni — Mon, 08 Feb 2016 05:03:22 GMT

Two ways to get the latest file.

1. If file is getting created on weekly basis, then while loading you can take file having max created date in that period.

Load all the file, store period & excel create time to identify maximum

2. Get only numbers like 12 from P1 W2 Finance.xls and store in one column & next column will be your filename. Then take maximum of that.

You table will have data like below

Period Week FileName

1 2 P1 W2 Filename

1 3 P1W3 Filename

etc

Re: QVD Logic

Anonymous — Mon, 08 Feb 2016 05:06:46 GMT

Manoj,

Thanks for the response

I think you have misunderstand my requirement

if p1week1, p1week2,p1week3 data i want to store only p1week3 data not all the data because p1week3 consists of week1 and week2's data.

Re: QVD Logic

Anonymous — Mon, 08 Feb 2016 05:08:03 GMT

Hello thanks for the response.

I am looking into it but couldnot able to figure out.

Re: QVD Logic

vikasmahajan — Mon, 08 Feb 2016 06:20:17 GMT

Check with this Kush has given nice script

Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd

HTH

Vikas

Re: QVD Logic

tamilarasu — Mon, 08 Feb 2016 06:41:33 GMT

Fine. Check the below one.

// Change the path
Set vPath = 'C:\Users\Tamilarasu.Nagaraj\Desktop\New folder (4)';

For Each vFile in Filelist('$(vPath)\*.xlsx')

Let vFileBase =  Replace(subfield('$(vFile)','\',SubStringCount('$(vFile)','\')+1),'.xlsx','');

Temp:
LOAD Mid('$(vFileBase)',5,1) as WeekNumber,
Mid('$(vFileBase)',2,1) as PeriodNumber,
subfield('$(vFile)','\',SubStringCount('$(vFile)','\')+1) as FileName
AutoGenerate 1;

Next vFile

Max:
LOAD
Max(WeekNumber) as MaxWeek,
Max(PeriodNumber) as MaxPeriod
Resident Temp;

LET vMaxWeek = peek('MaxWeek',-1,'Max');
LET vMaxPeriod = peek('MaxPeriod',-1,'Max');
DROP Tables Max,Temp ;

Let vMaxWeekile = 'P'& vMaxPeriod & ' W' & vMaxWeek & ' - Financials.xlsx';
Let vSheetName = 'Week ' & vMaxWeek;

Data:
LOAD [Client Reference],
     [Previous Weeks Total Incurred],
     [Current Total Incurred],
     [Incurred Movement],
     [Current Net Paid],
     [Current Remaining Reserve],
     [CL Claim Reference],
     [Accident Date],
     [Driver Name],
     Garage,
     Route,
     Type,
     [Incurred Status]
FROM
[$(vMaxWeekile)]
(ooxml, embedded labels, header is 2 lines, table is [$(vSheetName)]);

Store Data into 'Backup_P'$(vMaxPeriod)'W'$(vMaxWeek).qvd (qvd);

Re: QVD Logic

tamilarasu — Mon, 08 Feb 2016 06:56:20 GMT

I Guess, you have deleted the attachments for confidential reason. So, I have also deleted your attachments (Samples) from my reply.

Re: QVD Logic

PradeepReddy — Mon, 08 Feb 2016 07:14:44 GMT

check the attachment, it might be helpf

Re: script

Mon, 08 Feb 2016 09:29:41 GMT

Hi John,

See the attachment. are you looking for this kind of script?

Regards