topic Re: Tables link is creating a problem for data visualization in QlikView

Tables link is creating a problem for data visualization

Thu, 17 May 2012 13:03:58 GMT

Hello,

I've three data files with different fields. The fields are not matching in any table but, data is matching. To make a common filter in frontend i'm renaming the fields which are matching. Please go through my sample file. I'm creating a dynamic field for 3 tables for example,

Table1:

Load 'CAPAs' as ProductQuality;

Table2:

Load 'NCs' as ProductQuality;

Table3:

Load 'CC' as ProductQuality;

I've a datefield in all tables but the names are different. So i taken common name for all the tables and from that i created Year,Month,Day fields.Now when i select CC form ProductQuality listbox i need to see the years which belongs to table3 only i.e 2011,2012. But i can able to see the yrs which are not belongs to this table. Can any one please help me where i'm doing wrong.

Re: Tables link is creating a problem for data visualization

IAMDV — Thu, 17 May 2012 13:19:28 GMT

You are seeing the data for 2007 and 2008 because your calendar table is built using the TempDate field. However if you use the "Date" field then you won't see 2007 and 2008.

Probably something like this...

MasterCalender:

LOAD Brand,Year(Date) as Year,Month(Date) as Month, Day(Date) as Day

Resident TempCalender;

Cheers - DV

Re: Tables link is creating a problem for data visualization

Thu, 17 May 2012 13:25:43 GMT

DV,

But i'm not using date column, i'll use only year column. How can i overcome this?

Re: Tables link is creating a problem for data visualization

IAMDV — Thu, 17 May 2012 13:38:30 GMT

How about creating calculated field in the listbox? I mean ListBox using expression :

=Year([Date])

So that users can see only 2011 and 2012.

Cheers,

Re: Tables link is creating a problem for data visualization

IAMDV — Thu, 17 May 2012 13:47:10 GMT

This should work...

IF(Year([Date]) LIKE Year([TempDate]), Year([TempDate]))

Please look at the attachment.

Cheers - DV

www.QlikShare.com

Re: Tables link is creating a problem for data visualization

Thu, 17 May 2012 14:02:08 GMT

Hi DV,

Can you try with raw data which i've.

Re: Tables link is creating a problem for data visualization

IAMDV — Thu, 17 May 2012 21:15:55 GMT

I'll get back to you tomorrow.

Cheers - DV

Re: Tables link is creating a problem for data visualization

Fri, 18 May 2012 09:09:18 GMT

Hello,

I got the solution but i'm facing a problem with filtering. Can you see the attached image. I've choosen Brand as 1-Day Acuvue Colors and ProductQuality as CC. In my Straight table i'' get the values of CC as 2. Exactly i'm getting the values as per my database. Upto here everything is fine, With in our selections we have 2 dates i.e. u can see in CC_Date listbox. Year is 2011 & 2012, month is Jan and Nov. Actually CC value is 1 for Jan 2012 and 1 for Jan 2012 in DB, totally is 2. Now when i select the individual dates in CC_Date listbox it changes the CC value in straight table to 1. But when i select Year and Month listbox filters it won't changes.

Can any one help me on this. For your reference i'm attaching the sample file.

Re: Tables link is creating a problem for data visualization

jagannalla — Fri, 18 May 2012 09:42:47 GMT

CAPAs:
LOAD CP_NUMBER&[Product Type]&Date([Date Created],'DD/MM/YYYY') as Key, CP_NUMBER,
     Date([Date Created],'DD/MM/YYYY') as [Date Created],
     [Product Type]
FROM
CAPA_file.xlsx
(ooxml, embedded labels, table is [1Q2012 CAPAs]);
Brand:
LOAD Key,[Product Type] as Brand, 'CAPAs' as ProductQuality,Year([Date Created]) as Year,Month([Date Created]) as Month,Day([Date Created]) as Day Resident CAPAs;
NCs:
LOAD [Non Conformance No]&[Brand Name]&Date([Event Date],'DD/MM/YYYY') as Key,[Non Conformance No],
     [Brand Name],
     Date([Event Date],'DD/MM/YYYY') as [Event Date]
FROM
NCs_file.xlsx
(ooxml, embedded labels, table is Data);
Concatenate(Brand)
LOAD Key,[Brand Name] as Brand,'Ncs' as ProductQuality,Year([Event Date]) as Year,Month([Event Date]) as Month,Day([Event Date]) as Day Resident NCs;
CC:
LOAD CC&Brand&Date(Date,'DD/MM/YYYY') as Key,Brand as B1,
     Date(Date,'DD/MM/YYYY') as Date,
     CC
FROM
CustomerCompliant_file.xlsx
(ooxml, embedded labels, table is CC);
Concatenate(Brand)
LOAD Key,B1 as Brand,'CC' as ProductQuality,Year(Date) as Year,Month(Date) as Month,Day(Date) as Day Resident CC;

Re: Tables link is creating a problem for data visualization

IAMDV — Fri, 18 May 2012 11:26:21 GMT

Good job Jagan. Sorry I couldn't finish it for you.

Cheers - DV