topic Re: Creating Master Date Column for different date columns in a table in QlikView

Creating Master Date Column for different date columns in a table

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

Hello Experts,

I have table with multiple date columns (nearly 5 date columns are there), Now I want create one master date column for all of them because in my report i can't to show multiple list boxes for the user. If i show like that user will get confuse.

So, How to handle this issue i am not understanding. I have visited some threads also those are not useful for me

Please suggest some solutions and help me out from this task

Thank you in advance

marcowedel franky_h79‌vishsaggi‌

Regards,

B V S Sudhakar

Re: Creating Master Date Column for different date columns in a table

arvind_patil — Tue, 07 Aug 2018 06:32:17 GMT

HI Sudhakar,

Please provide sample data with your expected output so we will help you.

Thanks,

Arvind Patil

Re: Creating Master Date Column for different date columns in a table

BalaBhaskar_Qlik — Tue, 07 Aug 2018 06:36:50 GMT

Use Canonical Dates like this:

Canonical Date

Check this script:

QlikView Master Calendar - Multiple Dates | Qlik Community

Re: Creating Master Date Column for different date columns in a table

bvssudhakar — Tue, 07 Aug 2018 07:22:25 GMT

Hi Bala,

I tried with that script but it is giving error, because i have taken the data from sql server.

so that i thought it is not possible for mapping. can you give me the suggestion

Re: Creating Master Date Column for different date columns in a table

bvssudhakar — Tue, 07 Aug 2018 07:43:36 GMT

Hi Aravind,

Here i attached sample Excel sheet, In this table 5 to 6 date columns are there

Example: i am creating one bar chart/table for title name wise placements for this i use title name as dimension and =Count({<plcdate = {"*"}>}CandidateID) as expression

and now i have to create one list box for plcdate column

And now, again i am creating one bar chart/table for title name wise Declines for this i use title name as dimension and =Count({<Declinedate = {"*"}>}CandidateID) as expression

for this i have to create one more list box for Decline date column

Again i am creating one bar chart/table for title name wise Offered Candidates for this i use title name as dimension and =Count({<Offerdate = {"*"}>}CandidateID) as expression

for this i have to create one more list box for offer date column

But user is not accepting these many list boxes separately. they want only two list boxes year and month or hierarchy calendar list box that will apply to all date columns

This is the sample data and one of the major requirement from client

Please Help me out from this guys

Thank you in advance

Re: Creating Master Date Column for different date columns in a table

bvssudhakar — Tue, 07 Aug 2018 07:56:18 GMT

mbaeyens

Re: Creating Master Date Column for different date columns in a table

bvssudhakar — Thu, 09 Aug 2018 12:09:02 GMT

Hi stalwar1,

Here i attached sample Excel sheet, In this table 5 to 6 date columns are there

Example: i am creating one bar chart/table for title name wise placements for this i use title name as dimension and =Count({<plcdate = {"*"}>}CandidateID) as expression

and now i have to create one list box for plcdate column

And now, again i am creating one bar chart/table for title name wise Declines for this i use title name as dimension and =Count({<Declinedate = {"*"}>}CandidateID) as expression

for this i have to create one more list box for Decline date column

Again i am creating one bar chart/table for title name wise Offered Candidates for this i use title name as dimension and =Count({<Offerdate = {"*"}>}CandidateID) as expression

for this i have to create one more list box for offer date column

But user is not accepting these many list boxes separately. they want only two list boxes year and month or hierarchy calendar list box that will apply to all date columns

This is the sample data and one of the major requirement from client

Please Help me out from this guys

Thank you in advance

Re: Creating Master Date Column for different date columns in a table

MarcoWedel — Sun, 12 Aug 2018 22:32:30 GMT

Hi,

maybe one solution could be:

table1:
CrossTable (DateType, DateTemp)
LOAD jobid,   
    EnterDate,
    JobEndDate,   
    placementdate,
    plcdate, 
    offerdate,
    DeclineDate
FROM [https://community.qlik.com/servlet/JiveServlet/download/1527897-334459/RMS%20Sample%20Data.xlsx] (ooxml, embedded labels, table is Sheet1);

table2:
LOAD *,
    DayName(DateTime) as Date;
LOAD jobid,
    DateType,
    Timestamp(Alt(Timestamp#(PurgeChar(DateTemp,'T'),'YYYY-MM-DDhh:mm:ss.fffffff'),Timestamp#(DateTemp,'MM/DD/YYYY hh:mm:ss TT')),'MM/DD/YYYY hh:mm:ss.fff') as DateTime
Resident table1;

DROP Table table1;

tabCalendar: 
LOAD *, 
    Day(Date) as Day, 
    WeekDay(Date) as WeekDay, 
    Week(Date) as Week, 
    WeekName(Date) as WeekName, 
    Month(Date) as Month, 
    MonthName(Date) as MonthName, 
    Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter, 
    QuarterName(Date) as QuarterName, 
    Year(Date) as Year, 
    WeekYear(Date) as WeekYear;   
LOAD Date(MinDate+IterNo()-1) as Date 
While MinDate+IterNo()-1 <= MaxDate; 
LOAD Min(Date) as MinDate, 
    Max(Date) as MaxDate 
Resident table2;

hope this helps

regards

Marco