Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
parvez933
New Contributor III

Date

Hi Everyone,

I am generating dates with the below code

LET vMinDate=Num(MakeDate(2018,1,1));

let vMaxDate=Num(MakeDate(Year(today())+1,12,31));

Temp:
LOAD
Date($(vMinDate)+IterNo()-1) as TempDate
AutoGenerate(1)
While $(vMinDate)+IterNo()-1<=$(vMaxDate);

 

With the field TempDate, I got the dates from 01/01/2018 to 12/31/2019

I have an another field call as Festival, where there are list of holidays are listed

Festival:

left join(Temp)

Load * inline [

Holidays

01/01/2018

01/26/2018

02/14/2018

04/01/2018

];

(This is the sample table with list of holidays)

Now, I have to get a field called as Working days by separating the TempDate and Holidays

*****************************

I am trying to join the Temp table and Festival table and derive a table as 

T3:

Load

TempDate,

Holidays,

Date((TempDate-Holidays), 'MM/DD/YYYY') as WorkingDays

Resident Temp;

But, I dont know why I am getting incorrect WorkingDays

1.png

I am not able to understand why the years are generating as 1899 and 1900.

Can any one help me!!!!!

Tags (1)
1 Solution

Accepted Solutions
Partner
Partner

Re: Date

Hope below is what you want:

Date.PNG

Aiolos Zhao

View solution in original post

7 Replies
Partner
Partner

Re: Date

Hi Parvez,

Because when you use "TempDate-Holidays", you will get a number,

the number means the number of days between the TempDate and Holidays.

eg. 2018-01-02 - 2018-01-01 = 1

Actually I'm not really sure what you want, but you can use the "difference" to get what you want.

Aiolos Zhao

parvez933
New Contributor III

Re: Date

Hey Aiolos Zhao,

Thank You for your reply!

I am trying to calculate number of working days in a year, by subtracting TempDate and Holidays.

I have to get
TempDate-Holidays=dates of working days(WorkingDays)
Partner
Partner

Re: Date

Hi,
if what you want is only the number of days, I think you don't need to use the Date() function,
you can try to use the "TempDate-Holidays" directly, then you can get the number of days I think.
Aiolos Zhao
parvez933
New Contributor III

Re: Date

Hey Aiolos Zhao,

Thank You for your reply!

As you said, I am able to get the days b/w the dates with the expression TempDate-Holidays.

But, I want to show a filed where the holidays should be excluded.
Ex:
in TempDate we have all the dates from 01/01/2018 to 12/31/2019
01/01/2018
01/02/2018
.
.
.
.
etc
Let suppose, if 1st Jan 2018 is a holiday, it should be removed from the TempDate field and should be stored a separate file called "working Days"
WorkingDays
01/02/2018
01/03/2018
.
.
.
.
Here the 01/01/2018 is excluded as because it was a holiday!

Can you help me with that!!
Partner
Partner

Re: Date

Hope below is what you want:

Date.PNG

Aiolos Zhao

View solution in original post

parvez933
New Contributor III

Re: Date

Hey Aiolos Zhao,

Thank You for your reply!

This is the required o/p, I was looking!

I taught by subtracting we can achieve the filed. But by simply using the Not Exists() you made it!
Thank You for your time and solution!
Partner
Partner

Re: Date

You're welcome, glad to help you~