Max date in each of the file

[Anonymous]

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.

[sewialwork]

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/...)

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/ScriptRegularS...

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

Hope this will help)

AGB

[marcus_sommer]

Just try it with this approach:

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

- Marcus

[arvind_patil]

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

[Anonymous]

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.

[Anonymous]

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

[manoranjan_d]

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

[marcus_sommer]

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

[manoranjan_d]

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