Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
amit_gupta
Contributor III
Contributor III

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

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.

 

loveisfailstevedark‌@

8 Replies
Anonymous
Not applicable

Last working day Value

does this helps to you ?

amit_gupta
Contributor III
Contributor III
Author

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
Master II
Master II

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

Best Regards,
KC
tresesco
MVP
MVP

I guess, you would need: lastworkdate()

amit_gupta
Contributor III
Contributor III
Author

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
MVP
MVP

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
Contributor III
Contributor III
Author

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?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)