topic Re: Selecting last working day issue ( Looping) in QlikView

Do..while - Picking last working day's file issue ( Looping)

amit_gupta — Wed, 25 Nov 2020 16:16:04 GMT

We are getting files in folder for Qlik. File Format is:

ABC_20180609_231000

ABC_20180611_112000

ABC_20180612_123000

ABC_20180613_101000

From Monday to Friday, Files are generating, & we are fetching the latest file by using timestamp attached.

Our System is generating files on Working day also, though it is not having any data.

Our task is

if we are having 2 continues public holiday's, it should automatically pick last working day file by ignoring last 2 public holidays files.

For an example:

if today is 13-06-2018, and a public holiday for today and yesterday, It should ignore today's & previous day's file, and fetch 11-06-2018 files.

we are using master file for public holiday's but not getting any solutions.

loveisfail‌ stevedark‌@

Re: Selecting last working day issue ( Looping)

Anonymous — Mon, 11 Jun 2018 10:02:41 GMT

Last working day Value

does this helps to you ?

Re: Selecting last working day issue ( Looping)

amit_gupta — Mon, 11 Jun 2018 10:05:27 GMT

No, It won't work in our scenario.

because we are not having any static gap's between dates.

we are using Master table for date's

Re: Selecting last working day issue ( Looping)

jyothish8807 — Mon, 11 Jun 2018 10:12:23 GMT

Hi Amit,

You can use the logic of networkdays.

Identify thew min date and max date available in the file file name, Also maintain an excel with all the public holidays in it.

then identify the networkdays(maxdate,mindate,holidays)

Based on the network days identify the date, and the lookup the file based on the date.

Hope this approach might help.

Br,

Re: Selecting last working day issue ( Looping)

tresB — Mon, 11 Jun 2018 10:12:57 GMT

I guess, you would need: lastworkdate()

Re: Selecting last working day issue ( Looping)

amit_gupta — Mon, 11 Jun 2018 10:49:50 GMT

Thanks mate, I am not playing with charts. I am writing loop in script for latest file excluding the Public holiday and Weekends. So, I am maintaining the public holidays in spreadsheet. And, This is the script finally i have enclosed

LET VAUMax_Day = DATE(PEEK(Max(date#(left(right(FileName() ,19),15),'YYYYMMDD_hhmmss')),0,'DATETIME'),'YYYYMMDD_hhmmss');

Set a = 1;

Do while a<=7

Holiday:

LOAD

Date(Date#("AU Holiday",'DD-MM-YYYY'),'DD-MM-YYYY') as [AU Holiday],

Flag

FROM AU Holiday.csv

(txt, utf8, embedded labels, delimiter is ',', msq) Where [AU Holiday]='$(VAUMax_Day)'; //This returns as expected. Only problem here is i am not moving backward if previous date is public holiday.

LET b= NoOfRows('Holiday');

if b>0 then

LET VAUMax_Day=DATE(PEEK('Max_Day',0,'DATETIME'),'DD-MM-YYYY')-1;

endif

Let a=a+1;

Loop

Conclusion - My use case is If i have files in list like above(Original thread). From there i am pulling dates using FileName() after that i am creating max date and min date. But, When i tried above loop the variable it self not loading

Does it make sense?

- Amit

Re: Do..while - Picking last working day's file issue ( Looping)

tresB — Mon, 11 Jun 2018 11:03:31 GMT

Have you checked if your variable declaration statement is actually working?

LET VAUMax_Day = DATE(PEEK(Max(date#(left(right(FileName() ,19),15),'YYYYMMDD_hhmmss')),0,'DATETIME'),'YYYYMMDD_hhmmss');

Re: Do..while - Picking last working day's file issue ( Looping)

amit_gupta — Mon, 11 Jun 2018 11:13:52 GMT

Yup

FILEINFO:

LOAD

date#(left(right(FileName() ,19),15),'YYYYMMDD_hhmmss') as FullDate,

date#(left(right(FileName() ,19),8),'YYYYMMDD') as Date_M

FROM ABC_*.csv

(txt, utf8, embedded labels, delimiter is ',', msq);

DATETIME:

lOAD

MAX(FullDate) AS Max_Date,

MAX(Date_M) AS Max_Day

Resident FILEINFO;

LET vAUMaxDate=DATE(PEEK('Max_Date',0,'DATETIME'),'YYYYMMDD_hhmmss');

LET VAUMax_Day=DATE(PEEK('Max_Day',0,'DATETIME'),'DD-MM-YYYY');

Holiday:

LOAD

Date(Date#("AU Holiday",'DD-MM-YYYY'),'DD-MM-YYYY') as [AU Holiday],

Flag

FROM [lib://Connection/AU Holiday.csv]

(txt, utf8, embedded labels, delimiter is ',', msq) Where [AU Holiday]='$(vAUMaxDate)';

It is loading till one day, But i want to make it as Loop for all iteration

Re: Do..while - Picking last working day's file issue ( Looping)

Anil_Babu_Samineni — Mon, 18 Jun 2018 05:40:30 GMT

Thanks for recommending me, Can you try this?

SET vFILEPATH="lib://Dump";

FILEINFO:

LOAD

FileName() as File_name,

date#(left(right(FileName() ,19),15),'YYYYMMDD_hhmmss') as FullDate,

date#(left(right(FileName() ,19),8),'YYYYMMDD') as Date_M

FROM $(vFILEPATH)\ABC_*.csv

(txt, utf8, embedded labels, delimiter is ',', msq);

DATETIME:

lOAD

MAX(FullDate) AS Max_Date,

MAX(Date_M) AS Max_Day

Resident FILEINFO;

LET vAUMaxDate=DATE(PEEK('Max_Date',0,'DATETIME'),'YYYYMMDD_hhmmss');

LET VAUMax_Day=Num(DATE(PEEK('Max_Day',0,'DATETIME'),'YYYYMMDD'));

Previous_Date:

Load

max(date#(FullDate,'YYYYMMDD_hhmmss')) as YESTERDAYTIME

Resident FILEINFO WHERE num(Date_M)<'$(VAUMax_Day)';

LET vAUPrev_Date = DATE(PEEK('YESTERDAYTIME',0,'Previous_Date'),'YYYYMMDD_hhmmss');

Country:

LOAD *

FROM $(vFILEPATH)\ABC_$(vAUMaxDate)*.csv

(txt, utf8, embedded labels, delimiter is ',', msq);

SET @MyDate_Real = "=Date('$(VAUMax_Day)')";

Trace '$(@MyDate_Real)';

SET @MyDate= If(WeekDay('$(@MyDate_Real)') = 'Sun', Date('$(@MyDate_Real)'-2,'DD/MM/YYYY'),

If(WeekDay('$(@MyDate_Real)') = 'Sat', Date('$(@MyDate_Real)'-1,'DD/MM/YYYY'),

Date('$(@MyDate_Real)','DD/MM/YYYY')));

Trace '$(@MyDate)';

Holiday_List:

LOAD

"Holiday List" as Holiday

FROM $(vFILEPATH)\Public Holidays.csv

(txt, codepage is 28591, embedded labels, delimiter is '\t', msq);

Right Join (Holiday_List)

Load Holiday, COUNT(Holiday) as Holiday_Flag

Resident Holiday_List

where Holiday='$(@MyDate)'

Group By Holiday;

LET @Holiday_Flag = Peek('Holiday_Flag',0,'Holiday_List');

if '$(@Holiday_Flag)' > 0 Then

Do while '$(@Holiday_Flag)' >=1

set @MyDate=Date('$(@MyDate)'-1,'DD/MM/YYYY');

Right Join (Holiday_List)

Load Holiday, COUNT(Holiday) as Holiday_Flag

Resident Holiday_List

where Holiday='$(@MyDate)'

Group By Holiday;

LET @Holiday_Flag = Peek('Holiday_Flag',0,'Holiday_List');

Loop

End If

Note - Let me know if you need working file in SQL Server with image of format in CSV?

Does it helps?