Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
NickHoff
Specialist
Specialist

Straight Table Issue?

I'm new to Qlikview and I've been beating my head against this issue for the past week.  I have two tables one of them a master calendar generated from a script.  The other has implementation date and 20 columns of fiscal year total from FY2000-FY2020.

I build my two tables with the following and create the associate between the two tables by creating an alias of Date for Implementation Date and CalendarDate.  When I push Ctrl + t I see the association between the two tables, but when I create my straight table with an expression to sum my fiscal periods there is no functionality with the calendar table (meaning when I select CalendarYear or CalendarMonthName the straight table isn't affected....  Why is this?:

Fact:

LOAD Director,

     SSM,

     Analyst,

     [Category / Description],

     Category,

     [Contract Area],

     Condition,

     Division,

     RHM,

     [RHM Rollup],

     [FY01 Savings],

     [FY02 Savings],

     [FY03 Savings],

     [FY04 Savings],

     [FY05 Savings],

     [FY06 Savings],

     [FY07 Savings],

     [FY08 Savings],

     [FY09 Savings],

     [FY10 Savings],

     [FY11 Savings],

     [FY12 Savings],

     [FY13 Savings],

     [FY14 Savings],

     [FY15 Savings],

     [FY16 Savings],

     [FY17 Savings],

     [FY18 Savings],

     [FY19 Savings],

     [FY20 Savings],

     [Implementation Date] AS Date,

     [Validated Date],

     [Months of Savings],

     [Budget Category],

     URL

FROM

(ooxml, embedded labels);

Calendar:

LOAD CalendarDate AS Date,

     %DATE_KEY,

     CalendarDayOfMonth,

     CalendarDayName,

     CalendarWeekOfYear,

     CalendarMonthName,

     CalendarQuarter,

     CalendarYear,

     CalendarWeekNumberAndYear,

     CalendarMonthAndYear,

     CalendarQuarterMonthsAndYear,

     CalendarDayStart,

     CalendarWeekStart,

     CalendarMonthStart,

     CalendarQuarterStart,

     CalendarYearStart,

     CalendarDayEnd,

     CalendarWeekEnd,

     CalendarMonthEnd,

     CalendarQuarterEnd,

     CalendarYearEnd,

     CalendarQuarterAndYear,

     CalendarYearAndQuarter,

     CalendarWednesdays,

     FiscalMonthNum,

     FiscalYearDesc,

     %Fiscal_Key,

     FYRPR,

     FiscalYear,

     FiscalYearQuarter

FROM

(qvd);

1 Solution

Accepted Solutions
wallinpeter
Contributor III
Contributor III

Just add to top....

Don't forget to comment out the old Date field.

Also, your data is dirty, so values like "vary" and "varies" will obviously not convert.

Fact:

LOAD

     date( subfield( [Implementation Date],'/',3') & '-' & subfield( [Implementation Date],'/',1') & '-' & subfield( [Implementation Date],'/',2') ) as Date,

     Director,

     SSM,

     Analyst,

     [Category / Description],

     Category,

     [Contract Area],

     Condition,

     Division,

     RHM,

     [RHM Rollup],

     [FY01 Savings],

     [FY02 Savings],

     [FY03 Savings],

     [FY04 Savings],

     [FY05 Savings],

     [FY06 Savings],

     [FY07 Savings],

     [FY08 Savings],

     [FY09 Savings],

     [FY10 Savings],

     [FY11 Savings],

     [FY12 Savings],

     [FY13 Savings],

     [FY14 Savings],

     [FY15 Savings],

     [FY16 Savings],

     [FY17 Savings],

     [FY18 Savings],

     [FY19 Savings],

     [FY20 Savings],

    // [Implementation Date] AS Date,

     [Validated Date],

     [Months of Savings],

     [Budget Category],

     URL

FROM

(ooxml, embedded labels);

View solution in original post

12 Replies
bill_markham
Champion III
Champion III

Nick

Double check in both your tables that the Date field is really a true QlikView date field.

It is most likely to be adrift in your Fact Table.

Best Regards,     Bill

NickHoff
Specialist
Specialist
Author

If it's not really a true QlikView date field what are my options to join the two date fields?  There are several variances in the formatting of the date and some don't have a date they'll have character strings in it such as varries, varry, ect...

My Date field in the calendar table is formatted as the following:  36526 for 1/1/2000, 36527 for 1/2/2000.  The Date in the Fact table is formatted as 1/1/2000 or 1/01/2000, or 01/01/2000.

bill_markham
Champion III
Champion III

Nick

There are loads of ways in QlikView of massaging dodgy data into true dates, which one to use depends on the variety of dodginess.

Could you load your dates as is into a qvw and post them ?  Or just paste them into a reply as text ?

[Don;t post any sensitive data as this is a public forum]

Best Regards,     Bill

wallinpeter
Contributor III
Contributor III

format the date in your fact table as follows.....

date( subfield(OldDate,'/',3') & '-' & subfield(OldDate,'/',1') & '-' & subfield(OldDate,'/',2') ) as NewDate

NickHoff
Specialist
Specialist
Author

My Implementation Date as Date is the following:

2007
01/01/02
1/1/02
01/01/03
1/1/03
01/01/04
1/1/04
1/01/05
1/1/05
1/1/06
1/1/07
1/1/08
1/1/09
1/1/10
1/1/11
1/1/12
1/1/13
01/01/2002
01/01/2003
1/1/2003
1/1/2004
1/1/2005
1/1/2006
1/1/2011
1/1/2012
1/1/2013
1/1/2014
1/11/11
1/13/06
1/24/04
1/24/2004
1/12003
2/1/01
2/1/02
02/01/04
2/1/04
2/1/05
2/1/06
2/1/07
2/1/09
2/1/10
2/1/11
2/1/12
2/1/13
02/01/2002
2/1/2002
02/01/2003
02/1/2003
2/1/2003
2/1/2004
2/1/2011
2/1/2012
2/1/2013
2/1/2014
2/7/06
2/12/12
2/15/08
2/17/06
2/21/06
2/27/02
2/27/06
2/104
2/2003
3/1/02
3/1/04
03/01/05
3/1/05
3/1/06
3/1/07
3/1/09
3/1/10
3/1/11
3/1/12
3/1/13
3/1/2004
3/1/2011
3/1/2013
3/1/2103
3/1/12013
3/6/06
3/14/06
3/15/05
3/15/07
3/20/07
03/21/2003
3/30/02
03/30/03
3/30/06
3/30/2003
3/31/06
3/31/07
3/31/11
04/01/02
4/1/04
4/1/05
4/1/06
4/1/09
4/1/10
4/1/11
4/1/12
4/1/13
4/1/2004
4/01/2011
4/1/2011
4/1/2012
4/1/2013
4/17/02
4/17/07
4/30/02
4/30/07
5/1/02
5/1/04
5/1/05
5/1/06
5/1/07
5/1/09
5/1/10
5/1/11
5/1/12
5/1/13
5/1/2004
5/1/2005
5/1/2011
5/1/2013
5/1/2014
5/16/06
5/25/07
5/29/07
5/31/05
5/31/06
5/31/07
5/31/08
6/1/02
6/1/03
6/1/05
6/1/06
6/1/07
6/1/09
6/1/10
6/1/11
6/1/12
6/1/2002
6/1/2003
6/1/2004
6/1/2006
6/1/2011
6/1/2012
6/1/2013
6/1/2014
6/9/10
6/11/2002
6/14/07
06/14/2010
6/20/07
06/30/02
6/30/02
06/30/03
6/30/05
6/30/06
6/30/07
6/30/2003
7/1/01
07/01/02
7/1/02
07/01/03
7/1/03
07/01/04
7/1/04
7/1/05
7/1/06
7/1/07
7/1/08
7/1/09
7/01/10
7/1/10
7/1/11
7/1/12
7/1/13
7/1/14
07/01/2001
07/01/2002
7/1/2002
07/01/2003
7/1/2003
7/1/2004
7/1/2010
7/1/2011
7/1/2012
7/1/2013
7/1/2015
7/7/05
7/12/2012
7/20/05
7/25/06
7/30/01
7/30/06
7/31/07
08/01/02
8/1/02
8/1/04
8/1/05
8/1/06
8/1/08
8/1/09
8/1/10
8/1/11
8/1/12
8/1/2002
8/1/2004
8/1/2005
8/1/2010
8/1/2011
8/1/2012
8/1/2013
8/11/11
8/14/06
8/15/07
8/17/05
8/24/07
8/30/07
9/1/01
09/01/02
9/1/02
09/01/03
9/1/03
9/1/04
9/1/05
9/1/06
9/1/07
9/1/08
9/1/09
9/1/10
9/01/11
9/1/11
9/1/12
9/1/13
09/01/2002
9/1/2002
9/1/2003
9/1/2004
9/1/2011
9/1/2012
9/1/2013
9/7/06
9/15/05
09/18/2002
09/19/2002
9/21/06
9/23/04
9/25/05
9/27/07
9/28/06
9/30/02
9/30/07
9/30/11
10/1/01
10/01/02
10/1/02
10/1/04
10/1/05
10/01/06
10/1/06
10/1/07
10/1/08
10/01/09
10/1/09
10/1/10
10/1/11
10/1/12
10/1/2002
10/1/2003
10/01/2010
10/1/2010
10/1/2011
10/1/2012
10/1/2013
10/4/06
10/10/08
10/15/05
10/15/2005
10/17/11
10/17/2003
10/19/05
10/31/01
10/31/06
10/31/07
11/1/01
11/01/02
11/01/03
11/01/04
11/1/04
11/1/05
11/1/06
11/1/07
11/1/08
11/1/09
11/1/10
11/1/11
11/1/12
11/01/2002
11/1/2002
11/1/2004
11/1/2011
11/1/2012
11/1/2013
11/12/05
11/20/05
11/26/07
11/29/02
11/30/06
12/1//04
12/1/01
12/01/02
12/1/03
12/1/04
12/1/05
12/1/06
12/01/07
12/1/07
12/1/08
12/01/09
12/1/09
12/1/10
12/01/11
12/1/11
12/1/12
12/1/2002
12/1/2003
12/01/2005
12/1/2006
12/1/2010
12/1/2011
12/1/2012
12/1/2013
12/6/06
12/15/05
12/15/06
12/19/07
12/20/05
12/30/05
12/31/07
30/31/06
Varies
Various
vary
NickHoff
Specialist
Specialist
Author

Where would I put this in the Fact table Peter?

wallinpeter
Contributor III
Contributor III

Just add to top....

Don't forget to comment out the old Date field.

Also, your data is dirty, so values like "vary" and "varies" will obviously not convert.

Fact:

LOAD

     date( subfield( [Implementation Date],'/',3') & '-' & subfield( [Implementation Date],'/',1') & '-' & subfield( [Implementation Date],'/',2') ) as Date,

     Director,

     SSM,

     Analyst,

     [Category / Description],

     Category,

     [Contract Area],

     Condition,

     Division,

     RHM,

     [RHM Rollup],

     [FY01 Savings],

     [FY02 Savings],

     [FY03 Savings],

     [FY04 Savings],

     [FY05 Savings],

     [FY06 Savings],

     [FY07 Savings],

     [FY08 Savings],

     [FY09 Savings],

     [FY10 Savings],

     [FY11 Savings],

     [FY12 Savings],

     [FY13 Savings],

     [FY14 Savings],

     [FY15 Savings],

     [FY16 Savings],

     [FY17 Savings],

     [FY18 Savings],

     [FY19 Savings],

     [FY20 Savings],

    // [Implementation Date] AS Date,

     [Validated Date],

     [Months of Savings],

     [Budget Category],

     URL

FROM

(ooxml, embedded labels);

View solution in original post

NickHoff
Specialist
Specialist
Author

Thank you Bill and Peter you've helped me tremendously.

wallinpeter
Contributor III
Contributor III

No Problem