Tables link is creating a problem for data visualization

[#1]

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.

[IAMDV]

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

[#3]

DV,

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

[IAMDV]

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,

DV

[IAMDV]

This should work...

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

Please look at the attachment.

Cheers - DV

www.QlikShare.com

[#6]

Hi DV,

Can you try with raw data which i've.

[IAMDV]

I'll get back to you tomorrow.

Cheers - DV

[#8]

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.

[jagannalla · Accepted solution]

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;

[IAMDV]

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

Cheers - DV