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

[amit_gupta]

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‌@

[Anonymous]

Last working day Value

does this helps to you ?

[amit_gupta]

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

[jyothish8807]

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,

KC

[tresesco]

I guess, you would need: lastworkdate()

[amit_gupta]

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

[tresesco]

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

[amit_gupta]

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

[Anil_Babu_Samineni]

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?