topic Re: Link Table in QlikView

Link Table

Fri, 13 Sep 2013 06:01:03 GMT

Hi,

i have created a link table for master calender but it is not working fine. can any one help me

i have two table calldetails and indentdetails both are linked through callno. for one callno there may be 1 or more indents in indentdetails.

Calldetails:

callno

call_date

close_date

indentdetails:

indentNo

callNo

indentdate

now i want to create master calender for call_date, close_date and indentdate

for that im creating linktable but it is not working fine for indentdate.

LINK_TABLE:

LOAD callno as %CallLink,
CallDate as Call_Date,
TIME(MakeTime(Hour(CALLDATE),Minute(CALLDATE)),'HH:mm') as Call_Hour,
Left(TIME(MakeTime(Hour(CALLDATE),Minute(CALLDATE)),'HH:mm'),2) as Hrs,
'Open' as Link_Flag
Resident
Calldetails;

Concatenate

LOAD callno as %CallLink,
Resolved_date as Call_Date,
TIME(MakeTime(Hour(ResolvedDate),Minute(ResolvedDate)),'HH:mm') as Call_Hour,
Left(TIME(MakeTime(Hour(ResolvedDate),Minute(ResolvedDate)),'HH:mm'),2) as Hrs,
'Closed' as Link_Flag
Resident
Calldetails;

Concatenate

LOAD callno as %CallLink,
IndentDate as Call_Date,
TIME(MakeTime(Hour(INDENT_DATE),Minute(INDENT_DATE)),'HH:mm') as Call_Hour,
Left(TIME(MakeTime(Hour(INDENT_DATE),Minute(INDENT_DATE)),'HH:mm'),2) as Hrs,
'Indent' as Link_Flag
Resident
IndentDetails;

can any one help me please.

Re: Link Table

rajeshvaswani77 — Fri, 13 Sep 2013 06:43:56 GMT

Hi,

Check the date time format of the datetime fields in CallDetails and IncidentDetails table. Are they exactly as expected?

They could be different in format, or missing the time part and not matching.

thanks,

Rajesh Vaswani

Re: Link Table

Fri, 13 Sep 2013 07:07:31 GMT

no all are same used makedate for all three fields.

Re: Link Table

Peter_Cammaert — Fri, 13 Sep 2013 07:28:49 GMT

Make a Table Box with callno, the %CallLink and the three original dates. Check whether you are getting NULL values and whether they're OK.

I'm not sure what you're talking about when you say "it's not working fine". Could you give more details about what results you get?

BTW This is a weird construction, as you will be counting the same record more than once. Imagine for example that callno=12345 has a CallDate=1/1/2013, a single INDENT_DATE=1/3/2013 and a Resolved_date=1/4/2013. A single CallDetails row will now be linked to two different dates at the same time and a third time through IndentDetails. Is this your intention?

Peter

Re: Link Table

Fri, 13 Sep 2013 10:20:48 GMT

Hi,

As your tables are having one to many relationship , so u better not concatinate those two table "calldetails" and

"indentdetails" . Join Calldetail Into Indentdetail to form one table .Then take 3 times the resident of this table one time having call dete and other time having Close date and last as Indent date, concatinate all three and form a link table.

then link this common date field to your master calendar date.

I hope this helps.

@ Pratibha

Re: Link Table

Sat, 14 Sep 2013 09:58:02 GMT

thanks to all ill try it.