Solved: Re: Straight Table Issue? - Qlik Community

NickHoff · ‎2013-12-05

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

wallinpeter · ‎2013-12-05

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

Anonymous · ‎2013-12-05

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 · ‎2013-12-05

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 · ‎2013-12-05

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 · ‎2013-12-05

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

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

NickHoff · ‎2013-12-05

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 · ‎2013-12-05

Where would I put this in the Fact table Peter?

wallinpeter · ‎2013-12-05

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 · ‎2013-12-05

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

wallinpeter · ‎2013-12-05

No Problem