topic Re: Max date in each of the file in QlikView

Max date in each of the file

Anonymous — Wed, 22 Aug 2018 09:29:32 GMT

I have multiple excel files.

My requirement is to filter only for the records where the DOJ is max (DOJ column) in each of the excel files

I could easily do this taking one excel file , at a time creating variable finding max DOJ and like wise using same code for other excel separately and concatenating the final result.

In attached 3 sample data

1. 201801 - Here max DOJ (Month-Year) is Jan-18 so I want only 2 records I want

2. 201802- Here max DOJ (Month-Year) is Feb-18 so I want only 3 records I want

3. 201803- Here max DOJ (Month-Year) is Mar-18 so I want only 5 records I want

In final table would have total 10 r (5+2+3) records from the above 3 files.

So I am looking for a case how to achieve the result using iterative/loop steps to run on each of the file how to achieve it rather then creating separate tabs for each file and using same code in it.

Re: Max date in each of the file

sewialwork — Wed, 22 Aug 2018 10:54:34 GMT

Use smth like this first:

tmp:

LOAD

Month_Year,

EMPID,

DOJ,

FileName() as File //this is how u can find name of the file for each row (https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/FileFunctions/FileName.htm)

FROM

// this is how you can load any file from the folder

(ooxml, embedded labels, table is Sheet1);

Then group your data with conditions you need and your file name. Read more here:https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptRegularStatements/Load.htm

Dont forget to drop tables and fields you dont need anymore.

Hope this will help)

AGB

Re: Max date in each of the file

marcus_sommer — Wed, 22 Aug 2018 11:05:04 GMT

Just try it with this approach:

load * from Path\*.xlsx where date(floor(monthend(DOJ)), 'DD-MMM-YY') = Month_Year;

- Marcus

Re: Max date in each of the file

arvind_patil — Wed, 22 Aug 2018 11:10:41 GMT

Hi ,

Kindly try Below:

FinalData:

LOAD

'03' as Source,

Month_Year,

EMPID,

MonthStart(Date(DOJ)) as DOJ

FROM [lib://Bagic Master/201803.xlsx]

(ooxml, embedded labels, table is Sheet1) ;

Concatenate

LOAD

'02' as Source,

Month_Year,

EMPID,

MonthStart(Date(DOJ)) as DOJ

FROM [lib://Bagic Master/201802.xlsx]

(ooxml, embedded labels, table is Sheet1);

Concatenate

LOAD

'01' as Source,

Month_Year,

EMPID,

MonthStart(Date(DOJ)) as DOJ

FROM [lib://Bagic Master/201801.xlsx]

(ooxml, embedded labels, table is Sheet1);

Right Join (FinalData)

LOAD Source,

Max(DOJ) as DOJ

Resident FinalData

Group by Source;

Exit Script;

Thanks,

Arvind Patil

Re: Max date in each of the file

Anonymous — Wed, 22 Aug 2018 11:53:56 GMT

Thanks Arvind.

Bur the requirement is what if we are dealing with multiple file if in case

i hv 10 files i vl have to do the same for 10 files.

I am looking for other alternatives.

Re: Max date in each of the file

Anonymous — Wed, 22 Aug 2018 11:59:56 GMT

Hi Marcus,

Its but always DOJ might not be same as Month_Year

It could be that month year os 31-Mar-18 while for DOj max is 31-Jan-18 ..so we need to find max on basis of DOJ

Re: Max date in each of the file

manoranjan_d — Wed, 22 Aug 2018 12:04:28 GMT

may be like this

for each vFilename in filelist('path.filename.xls')

temp:

load

max(DOJ),

EMPID,

Monthyear

from$(vFilename)(biff...)

group by EMPID,MonthYear;

Next vFilename

store tablename into path.qvd

Re: Max date in each of the file

marcus_sommer — Wed, 22 Aug 2018 12:13:46 GMT

Then maybe with an inner join filter like:

excel:

load *, date(floor(monthend(DOJ)), 'DD-MMM-YY') as Filter, filebasename() as Source

from Path\*.xlsx;

inner join(excel)

load Source, max(Filter) as Filter resident excel group by Source;

- Marcus

Re: Max date in each of the file

manoranjan_d — Wed, 22 Aug 2018 12:18:05 GMT

may be this can help you hope so

for each vFilename in filelist('path.filename.xls')

temp1:

load

date(DOJ,'MMM-YY)) as DOJ1,

ID,

Month_Year

DOJ

from$(vFilename)(biff...)

Next vFilename

inner join

temp1:

load

max(date(DOJ)'MMM-YY)) as DOJ1,

from$(vFilename)(biff...)

Next vFilename

step1: retrieveall the record from path

step2: retrieve only the max(doj)month-year and make inner join from path