topic Re: What's wrong with this JOIN? in QlikView

What's wrong with this JOIN?

NickHoff — Thu, 05 Dec 2013 15:16:29 GMT

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

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

JOIN (Fact)

FactCalendar:

LOAD

[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],

Date,

[Months of Savings],

CalendarMonthName,

CalendarYear,

FiscalMonthNum,

FiscalYearDesc,

FiscalYear,

FiscalYearQuarter

WHERE [Implementation Date] = [Date]

Re: What's wrong with this JOIN?

Gysbert_Wassenaar — Thu, 05 Dec 2013 15:31:01 GMT

For one you don't specify where the data comes from. There's only a WHERE clause, but no From of Resident followed by a data source. And it looks like your trying to create a table with fields from the two tables you created earlier. If so you need something like:

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

FactCalendar:

Load

[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],

Date,

[Months of Savings]

resident Fact

join(FactCalendar)

Load

Date,

CalendarMonthName,

CalendarYear,

FiscalMonthNum,

FiscalYearDesc,

FiscalYear,

FiscalYearQuarter

resident Calendar;

Re: What's wrong with this JOIN?

NickHoff — Thu, 05 Dec 2013 15:33:50 GMT

I think I'm over thinking it, all I want to do is have my two tables joined by Implementation Date & Calendar Date. The Calendar Table is a master Calendar table generated by a script. To create the relationship I create an alias called date.

I can see the relationship when I push control T, but when I create my straightable the functionality of year and monthname being tied to the FY Savings isn't there.

Re: What's wrong with this JOIN?

maternmi — Fri, 06 Dec 2013 08:47:16 GMT

Hi Nick,

First, it seems you try to join field by creating a new table within the join statement. I think it won't work.

Second, you need a resident otherwise the source table of the known field can't be find.

JOIN (Fact)

FactCalendar: ---> a new table within the join statement

LOAD

[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],

Date,

[Months of Savings],

CalendarMonthName,

CalendarYear,

FiscalMonthNum,

FiscalYearDesc,

FiscalYear,

FiscalYearQuarter

WHERE [Implementation Date] = [Date]

resident --> missing

Re: What's wrong with this JOIN?

Peter_Cammaert — Fri, 06 Dec 2013 09:18:15 GMT

You're right, just leave the two tables resident. Forget about the JOIN statement, but let QlikView associate the two by way of the Date field. Make sure that the content of both fields is formatted in exactly the same way. Otherwise you won't have any reliable associative links.

Try for example a simple Date() around both fields, e.g. ...Date([Implementation Date]) as Date, ... in table Fact, and ...Date([CalendarDate]) as Date... in table Calendar.

A good key value debugging technique is to load both fields under their original names as well, and put the three of them in a Table box (e.g. [Implementation Date], Date and CalendarDate). Either the table box will show you a perfect correspondence, or you'll have two separate areas with a NULL value in the left or the right column (meaning there aren't any working associations).

Good luck,

Peter

Re: What's wrong with this JOIN?

brijesh1991 — Fri, 06 Dec 2013 10:04:37 GMT

Hi nick,

Data Source Path is missing.

If you are using both previous table, then resident statement is missing. ..