topic Re: Link Date Calendar to Link Table in QlikView

Link Date Calendar to Link Table

Tue, 22 Jan 2013 10:56:47 GMT

I am still relatively new to QlikView, however I require a Day, Month, Quarter & Year Filter (List Box) to link to my Link table, based on the dates I select the object will dynamically analyses my Data model. However when I do link it to Inline table I receive an error stating Ambiguous results. My table looks like image attached, I have removed the Day, Month, Year Inline Tables, if someone could please help me.

Re: Link Date Calendar to Link Table

Tue, 22 Jan 2013 11:06:31 GMT

He Gerhard,

You usually get the ambiguous results if there is some 'loop/circular reference' in your data model. For example table 1 is connected to table 2 and table 3, table 2 is connected to table 3.

You should remove these circular references.

hope this helps.

gr.

Frank

Re: Link Date Calendar to Link Table

fernando_tonial — Tue, 22 Jan 2013 11:10:21 GMT

Hi Gerhard,

You can try this.

Create 2 variables.

LET vMonthStart = Date('01/01/2006');

LET vMonthFinish = Date(MonthEnd(Today()));

Create a table using the variables and AUTOGENERATE comand.

CALENDAR:

LOAD

[Date] as Date,

Day(Date) as Day,

Month(Date) as Month,

Year(Date) as Year,

MonthName(Date) as [MonthYear],

ceil(Month(Date)/3)&'º Q' as [Quarter];

LOAD

Date(MonthEnd(Today())- Recno()+1,'DD/MM/YYYY') as [Date]

AUTOGENERATE(vMonthFinish-vMonthStart+1);

Best Regards,

Tonial.

Re: Link Date Calendar to Link Table

Tue, 22 Jan 2013 11:33:45 GMT

@fernando.tonial, finally got the date to link with teh LINK table, however if I make any selection (lets say 2012) all my object loose all there data, no in my mind if I select 2012 from calander, it should link to LINK table that is also linked to Orders, so all the orders with CreatedDate (**/**/2012) should be previewed.

Any advice on this?

Re: Link Date Calendar to Link Table

er_mohit — Tue, 22 Jan 2013 11:44:41 GMT

see the attached file

Re: Link Date Calendar to Link Table

fernando_tonial — Tue, 22 Jan 2013 11:49:22 GMT

You must verify the format of the fields that make the association between the tables. In this case the field is the Date.

Re: Link Date Calendar to Link Table

Tue, 22 Jan 2013 12:05:51 GMT

Found out that my Link table only pull the first table data, not the rest:

//Company Date

Link:

LOAD

compID

,Comp_CreatedDate as Date

,'Comp_Created' as DateType

RESIDENT Company

;

// Cases Date

CONCATENATE (Link)

LOAD

caseID

,Case_CreatedDate as Date

,'Case_Created' as DateType

RESIDENT Cases

;

// Person Date

CONCATENATE (Link)

LOAD

personID

,Pers_CreatedDate as Date

,'Pers_Created' as DateType

RESIDENT Person

;

// Opportunity

CONCATENATE (Link)

LOAD

oppID

,Oppo_CreatedDate as Date

,'Oppo_Created' as DateType

RESIDENT Opportunity

;

// Lead

CONCATENATE (Link)

LOAD

leadID

,Lead_CreatedDate as Date

,'Lead_Created' as DateType

RESIDENT Lead

;

Re: Link Date Calendar to Link Table

er_mohit — Tue, 22 Jan 2013 12:21:36 GMT

write this code in every load on first

Date(Date#(Date,'DD-MM-YYYY'))as ABC,

i think it might be help you..

all procedure same only add this code in every load at starting time like

//Company Date

Link:

LOAD

Date(Date#(Date,'DD-MM-YYYY'))as ABC,

compID

,Comp_CreatedDate as Date

,'Comp_Created' as DateType

RESIDENT Company

if this can't helps you then

replace concatenate and use inner join

Re: Link Date Calendar to Link Table

Tue, 22 Jan 2013 13:13:08 GMT

With no luck er.mohit

The LINK table only pulls the first (Company) table and has the other fields as columns with no values, I have tried the Date as well the Inner Join with no luck.

Any other suggestions?

Re: Link Date Calendar to Link Table

er_mohit — Tue, 22 Jan 2013 13:15:44 GMT

send me your overall script.

i ll try my best.

Re: Link Date Calendar to Link Table

Tue, 22 Jan 2013 13:39:00 GMT

Thank you er.mohit

Every time I would like the tables and then to the Link table I get duplicates, other issue is there is no Master table

Re: Link Date Calendar to Link Table

er_mohit — Wed, 23 Jan 2013 05:52:28 GMT

see the attached code