Straight Table Issue?

[NickHoff]

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);

[Anonymous]

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]

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.

[Anonymous]

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]

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

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

[NickHoff]

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]

Where would I put this in the Fact table Peter?

[wallinpeter · Accepted solution]

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);

[NickHoff]

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

[wallinpeter]

No Problem