topic Multiple dates in same table in QlikView

Multiple dates in same table

neha_shirsath — Thu, 12 Jan 2017 07:28:38 GMT

Hello Community,

I'm facing issue with multiple dates.

I've one table containing created date, release date and changed on date and need to create calendar(here i'm using created date for calendar).

I'm attaching the sample data in that if i'm selecting created date as 2 then i want to see all the lot released on 2 and changed on 2.

so how we can do this on set analysis/in script?

Thanks in advance.

-Neha

Re: Multiple dates in same table

marcus_sommer — Thu, 12 Jan 2017 08:06:51 GMT

Take a look here:

Canonical Date

Why You sometimes should Load a Master Table several times‌

- Marcus

Re: Multiple dates in same table

neha_shirsath — Thu, 12 Jan 2017 08:12:01 GMT

Hi thanks for the reply.

I've already read this post but in my case not useful as I want to give single calendar selection for the user.

that's why i was thinking if I use the set analysis for the same.

Thanks.

Re: Multiple dates in same table

neha_shirsath — Thu, 12 Jan 2017 08:21:24 GMT

See the attached app.

I used the set analysis in file but when i'm selecting 2 as date in chart i'm getting 1 as date because my calendar is on created date but i want to see selected date .

Re: Multiple dates in same table

MK_QSL — Thu, 12 Jan 2017 08:57:56 GMT

Try to upload proper sample! Here it will not make sense providing numbers 1,2,3, instead of date in date field.

Re: Multiple dates in same table

marcus_sommer — Thu, 12 Jan 2017 09:01:22 GMT

A master-calendar on a canonical date is aimed to provide a single calendar for multiple date-fields whereby it didn't exclude further calendars which might be useful for other more particular sights, too.

To implement this isn't easy and needs some requirements, like a logically connection between the dates and a common key. I think if you searched for canonical date within the community you will find various examples for it.

- Marcus

Re: Multiple dates in same table

petter — Thu, 12 Jan 2017 09:02:33 GMT

I think this small application might help you see how Set Analysis (Set Expression) works when you need to compare several dates. I have used the Input Box to let you play around with the expressions interactively and quick and get an immediate result in four different tables:

The load script - with an added field named UniqueID which is necessary to get "search within set expressions" to work correctly.

Re: Multiple dates in same table

neha_shirsath — Thu, 12 Jan 2017 10:32:37 GMT

Hi Manish,

Thanks for the reply.

Please find the attached updated excel file.

Basically the number was indicating date month number.

-Neha

Re: Multiple dates in same table

neha_shirsath — Thu, 12 Jan 2017 10:40:07 GMT

Hi Petter,

Thank you so much for your detail specification.

This should work on selection on date field rather than unique id field.

How should I do that?

My Requirement is like-

When my selection is Month(Created date) i.e suppose Feb

then I want to see all the lot Created in FEB,Released on FEB and changed in FEB like so on.

-Neha

Re: Multiple dates in same table

MK_QSL — Thu, 12 Jan 2017 11:51:04 GMT

Data:
Load * Inline
[
lot created released changed_on
Lot1 01/01/2016 02/02/2016 03/03/2016
Lot2 01/01/2016 02/02/2016 03/03/2016
Lot3 01/01/2016 03/03/2016 03/03/2016
Lot4 01/01/2016 03/03/2016 03/03/2016
Lot5 02/02/2016 03/03/2016 03/03/2016
Lot6 02/02/2016 04/04/2016 03/03/2016
Lot7 02/02/2016 05/05/2016 03/03/2016
Lot8 03/03/2016 06/06/2016 04/04/2016
Lot9 03/03/2016 07/07/2016 04/04/2016
Lot10 04/04/2016 07/07/2016 04/04/2016
Lot11 04/04/2016 07/07/2016 05/05/2016
Lot12 04/04/2016 07/07/2016 06/06/2016
Lot13 04/04/2016 08/08/2016 08/08/2016
Lot14 04/04/2016 08/08/2016 09/09/2016
Lot15 04/04/2016 08/08/2016 09/09/2016
] (delimiter is \t);


Map_Cretaed:
Mapping Load lot, created Resident Data;


Map_released:
Mapping Load lot, released Resident Data;


Map_changed:
Mapping Load lot, changed_on Resident Data;


Final:
Load lot, 'created' as Flag, ApplyMap('Map_Cretaed',lot,'unknown') as CommonDate Resident Data;
Load lot, 'released' as Flag, ApplyMap('Map_released',lot,'unknown') as CommonDate Resident Data;
Load lot, 'changed' as Flag, ApplyMap('Map_changed',lot,'unknown') as CommonDate Resident Data;


Drop Table Data;


Calendar:
MasterCalendar: 
Load 
  Date(TempDate) as CommonDate,
  Week(TempDate) as Week,
  Month(TempDate) as Month
;
Load 
  MinDate + IterNo() - 1 as TempDate
While MinDate + IterNo() - 1 <= MaxDate;
Load
  Min(FieldValue('CommonDate',RecNo())) as MinDate,
  Max(FieldValue('CommonDate',RecNo())) as MaxDate
AutoGenerate FieldValueCount('CommonDate');

Re: Multiple dates in same table

nirav_bhimani — Thu, 12 Jan 2017 11:56:32 GMT

Hi Neha,

Please find the attached file. You have to use the date island table.

hope this will help you.

Regards,

Nirav Bhimani

Re: Multiple dates in same table

petter — Thu, 12 Jan 2017 12:32:17 GMT

First of all: The UniqueID field is just a helper field to collect the rows that results from a search. The comparison you write in between the double quotes is what matters - trust me.

This pose as a new question ... but anyways - I updated the sample application with real dates not like the ones you showed as sample data. And the approach still works like using the Month()-function. Notice how the search string looks like now.

I had to create some randomized test data and introduce the dates as real dates so the load script looks like this:

Re: Multiple dates in same table

neha_shirsath — Thu, 12 Jan 2017 12:34:05 GMT

Hi Manish,

Have use your code and put the below set expression-

=count({<CommonDate={$(=Month(max(CommonDate)))},CommonDate=>}DISTINCT lot)

And its working with the result i wanted.

Thank you so much.

-Neha

Re: Multiple dates in same table

neha_shirsath — Thu, 12 Jan 2017 12:44:54 GMT

Thank you guys for your prompt reply.

It's helps me a lot to think out of the box.

petter-s‌, Thanks for your precious time, will try this solution also.

-Neha