Filter using Dimension in the Expression of chart

[balajiqlikuser]

Hi all,

I am creating a chart with Date1 as Dimension. In the Expression, I need to calculate a penetration metric where the numerator is from Table A and Denominator from Table B. Both the tables are joined using Column1.  The dimension (Date1) is from Table A. The Numerator sums the sales group by Date1 and the denominator should sum the sales group by Date2 where Date2 should be equal to Date1. How to give this condition in the expression? Any ideas please. 

[edwin]

i suggest a slight tweak to your data model.  instead of joining by COLUMN1, it sounds like your key isnt just Column1.  join the two tables using a composite of COLUMN1 + DATE.  be careful that both date1 and date2 are same format and FLOORED if one is a time stamp

COLUMN1 & '|'  & DATE1 as %Key  // in table1
COLUMN1 & '|'  & DATE2 as %Key // in table2

now your expression is

sum(measure_from_table1) / sum(measure_from_table2)  // or whatever

[balajiqlikuser]

Thanks @edwin . I thought this but then my table 1 and table 2 has date3 and date4 respectively and same calculation needs to be done. So do i need to create two keys in Table 1 and join with Table 2 twice with two different dates?

[edwin · Accepted solution]

if i understand your data model properly:

Table1:
Column1
Date1
Date3
SomeMeasure1

Table2:
Column1
Date2
Date3
SomeMeasure2

and you have 1 calculation that joins the two tables on Column1 and Date1=Date2;  another cal using Column1 and Date3= Date4.

assuming you cant denormalize them , the proper way of linking these two fact tables is via LINK table.
but the simpler solution from top of my head would be the  following (assuming Table1 is bigger):

Table1:
Column1
Date1
Date3
Column1 & '|' & Date1 as %Key1
Column1 & '|' & Date3 as %Key2
Date3SomeMeasure1

noconcatenate
Table2a:
Column1 & '|' & Date2 as %Key1,
SomeMeasure2 as SomeMeasure2a
resident Table2;

noconcatenate
Table2b:
Column1 & '|' & Date4 as %Key2,
SomeMeasure2 as SomeMeasure2b
resident Table2;
drop table Table2;

ill post separate reply for link table

[edwin]

to create link:

in each table create the following:

Table1 - Column1 & '|' & Date1 & '|' & Date3 as %LinkKey1

Table2 - Column1 & '|' & Date2 & '|' & Date4 as %LinkKey2

then create the link table assuming Table1 is the driving table (if there is no entry in table1, you dont care about table2 - like a left join):

//we need the distinct of the keys
noconcatenate
tmpTable1:
load distinct 
%LinkKey1,Column1 as Column11,Date1,Date3
resident Table1;

noconcatenate
tempTable2:
load distinct
%LinkKey2,Column1 as Column12,Date2,Date4
resident Table2;

//starting the link tables
noconcatenate
LinkTable:
load * resident tmpTable1;

//get corresponding entries in table2 where Date1 = Date2
left join (LinkTable)
load %LinkKey2,
'FIRST' as LinkType
where Column11 = Column12
and Date1 = Date2;

//get corresponding entries in table2 where Date3 = Date4
left join (tmpTable1)
load %LinkKey2,
'SECOND' as LinkType
where Column11 = Column12
and Date3 = Date4;

//add to link table
concatenate (LinkTable)
load * resident tmpTable1;

//drop temp tables and fields
drop table tmpTable1, tmpTable2;
drop fields Column11, Date1, Date3 from LinkTable;

hope this helps

[edwin]

pls not i may have done a short cut here with the left join.  i wrote this outside of qlikview.  what you ay have to do is left join first without the where clause then create a new table filtering unwanted rows using the where clause.

[balajiqlikuser]

This was what i thought and did. Thanks for your time!

[balajiqlikuser]

I am not sure about the Link Table stuffs. Anyways, thanks for this solution also. I will try.