topic Re: Date in QlikView

Date

parvez933 — Thu, 27 Dec 2018 05:29:14 GMT

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

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

Can any one help me!!!!!

Re: Date

uacg0009 — Thu, 27 Dec 2018 06:43:50 GMT

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

Re: Date

parvez933 — Thu, 27 Dec 2018 06:49:41 GMT

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)

Re: Date

uacg0009 — Thu, 27 Dec 2018 06:52:46 GMT

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

Re: Date

parvez933 — Thu, 27 Dec 2018 07:00:38 GMT

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

Re: Date

uacg0009 — Thu, 27 Dec 2018 07:09:39 GMT

Hope below is what you want:

Aiolos Zhao

Re: Date

parvez933 — Thu, 27 Dec 2018 07:16:12 GMT

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!

Re: Date

uacg0009 — Thu, 27 Dec 2018 07:17:52 GMT

You're welcome, glad to help you~