topic Re: Date Issue Reading Date Format in QlikView

Date Issue Reading Date Format

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

Hello,

I have dates that are 01/10/2018 and 1/12/2018 so depending on what selection I make it's not reading both. How can I fix that?

Thank you!

Re: Date Issue Reading Date Format

sunny_talwar — Fri, 06 Apr 2018 12:57:08 GMT

Not sure I understand your issue? You have two different formats? How do they differ and what are you expecting?

Re: Date Issue Reading Date Format

hammermill21 — Fri, 06 Apr 2018 13:00:34 GMT

Hey Sunny!

Some of my dates are 01/10/2018 the month is MM format then some are 1/10/2018 so the Month is M format. I need them all to be in MM/DD/YYYY format and I'm not sure what that is happening since the data I'm pulling is in MM/DD/YYY format.

I'm not sure if it's something that is wrong with my Master Calendar:

MinMaxDate:

Load

min([Get Date]) as MinDate,

max([Get Date]) as MaxDate

resident [root];

Let vMinDate = Peek('MinDate',0,'MinMaxDate');

Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate');

Let vToday = $(vMaxDate);

drop table MinMaxDate;

TempCal:

Load

date($(vMinDate) + rowno()-1) as TempDate

autoGenerate($(vMaxDate)-$(vMinDate)+1);

Mastercalendar:

Load

TempDate as [Get Date],

year(TempDate) as Year, //this is so you can select by year

month(TempDate) as Month, //this is so you can select by month and so on

monthname(TempDate) as MonthYear,

day(TempDate) as Day,

weekday (TempDate) as WeekDay,

week(TempDate) as Week,

year(yearstart(TempDate,0,4)) as FiscalYear, // means starting same time every year but the 4th month

'Q' & ceil(month(TempDate)/3) as Quarter, //ceil means to round up; 'Q" & is so you can have Q1, Q2..

InYearToDate(TempDate,$(vToday),0) * -1 as CurYTDFlag,

InYearToDate(TempDate,$(vToday),-1) * -1 as LastYTDFlag

resident TempCal;

drop table TempCal;

Re: Date Issue Reading Date Format

sunny_talwar — Fri, 06 Apr 2018 13:05:53 GMT

Check/Modify two things

1) Your environmental variables have this

SET DateFormat='MM/DD/YYYY';

2) Make sure to do this in your fact table

Date([Get Date]) as [Get Date]

and in your master calendar

Date(TempDate) as [Get Date],

By doing this, you will pick the Date format from Environmental variable and will have it consistently the same across each cell

Re: Date Issue Reading Date Format

hammermill21 — Fri, 06 Apr 2018 13:25:18 GMT

Let me make sure I understand:

In my Main table I have this:

LOAD TEXT ([Barcode]) AS [Bascom Barcode],

[Username] AS [Bascom Username],

[Inspection Date] AS [Get Date],

Then in my Master Calendar, I have this:

Mastercalendar:

Load

Date(TempDate) as [Get Date],

year(TempDate) as Year,

month(TempDate) as Month,

monthname(TempDate) as MonthYear,

day(TempDate) as Day,

weekday (TempDate) as WeekDay,

week(TempDate) as Week,

year(yearstart(TempDate,0,4)) as FiscalYear,

'Q' & ceil(month(TempDate)/3) as Quarter,

InYearToDate(TempDate,$(vToday),0) * -1 as CurYTDFlag,

InYearToDate(TempDate,$(vToday),-1) * -1 as LastYTDFlag

resident TempCal;

drop table TempCal;

Is that what you mean?

Thank you Sunny for your help as always.

Re: Date Issue Reading Date Format

sunny_talwar — Fri, 06 Apr 2018 13:31:37 GMT

Step 1) make sure your environmental variable to have the right date format

Step 2)

LOAD TEXT ([Barcode]) AS [Bascom Barcode],

[Username] AS [Bascom Username],

Date([Inspection Date]) AS [Get Date],

Master calendar is good

Re: Date Issue Reading Date Format

hammermill21 — Fri, 06 Apr 2018 13:34:49 GMT

Ah! I missed the "environmental variable" part and I had tried the Date([Inspection Date]) AS [Get Date] but got an error and thought it was wrong. It's that my environmental value was off.

This helps a lot, thank you

Re: Date Issue Reading Date Format

sunny_talwar — Fri, 06 Apr 2018 13:35:42 GMT

Super

Re: Date Issue Reading Date Format

hammermill21 — Fri, 06 Apr 2018 13:46:53 GMT

Another question regarding the Dates... Sorry everytime I think I got it, I don't! lol

So now that I have my date working perfectly when I add a filter by "Month" it's not calculating correctly. Yet if I select all the individual dates my numbers are right. Does that have to do with my master calendar? Should I be adding something in my script to format the Month differently?

Re: Date Issue Reading Date Format

sunny_talwar — Fri, 06 Apr 2018 13:51:27 GMT

So now that I have my date working perfectly when I add a filter by "Month" it's not calculating correctly.

Not calculating correctly? You will have to elaborate what is going on?

Re: Date Issue Reading Date Format

hammermill21 — Fri, 06 Apr 2018 13:54:45 GMT

So when I select all Jan dates:

I get an inventory count of 261, which is correct.

Now if I select just the "month" Jan:

I get an inventory count of 42, so I'm guessing it's not grabbing all the Jan dates when I select Month Jan.

Re: Date Issue Reading Date Format

sunny_talwar — Fri, 06 Apr 2018 13:57:01 GMT

Two questions

1) Are you sure you selected all Jan dates? I can't see the whole filter object... so can't really tell

2) What is the expression you have used?

Re: Date Issue Reading Date Format

hammermill21 — Fri, 06 Apr 2018 14:14:30 GMT

Yes, I have selected all dates, I just checked again.

For the Date, my expression is just Get Date; for the Month I have expression Month.

Then for my inventory count, my expression is:

Count({<[Bascom Item Type]={'FIRE EXTINGUISHER'},[Bascom Pass]= {'YES'}>} [Bascom Item Type])

I am counting how many Fire Extinguishers there are that have Passed Inspection.

Re: Date Issue Reading Date Format

sunny_talwar — Fri, 06 Apr 2018 14:17:10 GMT

Don't really know... it should work...

When you select Month... what dates get selected?

Re: Date Issue Reading Date Format

hammermill21 — Fri, 06 Apr 2018 14:24:29 GMT

It selects most dates in Jan except 01/04/2018, 01/05/2018 and 01/07/2018. It's so odd!!

And I don't see anything different in the data I am pulling:

Example of one recorded dates 01/07/2018:

<Inspection_x0020_Date>01/07/2018</Inspection_x0020_Date>

<Inspection_x0020_Time>02:33 PM</Inspection_x0020_Time>

Example of data record dated 01/31/2018:

<Inspection_x0020_Date>01/30/2018</Inspection_x0020_Date>

<Inspection_x0020_Time>07:13 AM</Inspection_x0020_Time>

Re: Date Issue Reading Date Format

sunny_talwar — Fri, 06 Apr 2018 14:28:22 GMT

Can you share your complete script...

Re: Date Issue Reading Date Format

hammermill21 — Fri, 06 Apr 2018 14:31:11 GMT

Of Course:

RestConnectorMasterTable:

SQL SELECT

"Barcode",

"Username",

"Inspection Date",

"Inspection Time",

"Item Type",

"Building",

"Floor",

"Location",

"Room Number",

"Description",

"Comments",

"Pass",

"Failure Mode",

"DateAdded",

"DateRetired",

"ReasonRetired",

"Make",

"Manufacturer",

"ModelNo",

"OccupancyType",

"InspectionFrequency",

"New Barcode",

"UserDef3"

FROM JSON (wrap on) "root";

[root]:

LOAD TEXT ([Barcode]) AS [Bascom Barcode],

[Username] AS [Bascom Username],

Date([Inspection Date]) AS [Get Date],

[Inspection Time] AS [Bascom Inspection Time],

[Item Type] AS [Bascom Item Type],

[Building] AS [Bascom Building],

[Floor] AS [Bascom Floor],

[Location] AS [Bascom Location],

[Room Number] AS [Bascom Room Number],

[Description] AS [Bascom Description],

[Comments] AS [Bascom Comments],

[Pass] AS [Bascom Pass],

[Failure Mode] AS [Bascom Failure Mode],

[DateAdded] AS [Bascom DateAdded],

[DateRetired] AS [Bascom DateRetired],

[ReasonRetired] AS [Bascom ReasonRetired],

[Make] AS [Bascom Make],

[Manufacturer] AS [Bascom Manufacturer],

[ModelNo] AS [Bascom ModelNo],

[OccupancyType] AS [Bascom OccupancyType],

[InspectionFrequency] AS [Bascom InspectionFrequency],

[New Barcode] AS [BascomNew Barcode],

[UserDef3] AS [UserDef3]

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

Re: Date Issue Reading Date Format

sunny_talwar — Fri, 06 Apr 2018 14:33:20 GMT

Where is the script after this that creates the calendar

Re: Date Issue Reading Date Format

hammermill21 — Fri, 06 Apr 2018 14:37:33 GMT

After that I have the master Calendar which I shared before:

And that's it

MinMaxDate:

Load

min([Get Date]) as MinDate,

max([Get Date]) as MaxDate

resident [root];

Let vMinDate = Peek('MinDate',0,'MinMaxDate');

Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate');

Let vToday = $(vMaxDate);

drop table MinMaxDate;

TempCal:

Load

date($(vMinDate) + rowno()-1) as TempDate

autoGenerate($(vMaxDate)-$(vMinDate)+1);

Mastercalendar:

Load

Date(TempDate) as [Get Date],

year(TempDate) as Year,

month(TempDate) as Month,

monthname(TempDate) as MonthYear,

day(TempDate) as Day,

weekday (TempDate) as WeekDay,

week(TempDate) as Week,

year(yearstart(TempDate,0,4)) as FiscalYear,

'Q' & ceil(month(TempDate)/3) as Quarter,

InYearToDate(TempDate,$(vToday),0) * -1 as CurYTDFlag,

InYearToDate(TempDate,$(vToday),-1) * -1 as LastYTDFlag

resident TempCal;

drop table TempCal;

Re: Date Issue Reading Date Format

sunny_talwar — Fri, 06 Apr 2018 14:42:03 GMT

All this look pretty usual. I am not sure I know why the dates are getting dropped