Canonical date - duplicated results

[Anonymous]

hi all,

i am fairly new to Qlikview, and was hoping to get some help on the following question:

i am trying to create a "canonical date" (as reffered in a post which i read), my motivation of doing it is i have 3 different dimension of dates, that i want to insert into a single calendar table, that enables breakdown by creating a flag

I have 3 dates: (the industry is online advertising)

1. Sale date

2. Activation date

3. Click date

the problem is that i see larger results than i have, so i believe i am duplicating results somehow,

i have a feeling it should be on the 3rd load statement,

therefore, i add a distinc, but apperantly it didn't solved it:

Date_Type:

LOAD

  JobId,

  date(daystart(SaleDate)) as Date,

  'SaleDate' as DateType

Resident FactMonthlyConversion;

LOAD

  JobId,

  date(daystart(ActivationDate)) as Date,

  'ActivationDate' as DateType

Resident FactActivations;

LOAD

  distinct JobId,

  ClickDate as Date,

  'ClickDate' as DateType

Resident FactJobPerformance;

[marcus_malinow]

Hi Nir,

What's the expression you're using?

Maybe count(JobId)?

If so, this will result in inflated figures. You should use set analysis to restrict values based on DateType.

For example

count({$<DateType={'SaleDate'}>} JobId)

Marcus

[Anonymous]

Hey Marcus,

basically i am using a sum function,

i am using:

sum({$<DateType={'ClickDate'}>} TotalClicks)

i have a feeling that the problem lays in the script, becuase:

the Date_Type key table is composed from 3 tables:

1. FactMonthlyConversion (1M rows)

2. FactActivations (10M rows)

3. FactJobPerformance (42M rows)

but the Date_Type table has is larger than the sum of these (see picture attached)

[marcus_malinow]

The DateType table is ok.

The script is showing a cumulative count of the data.

So 1m for the first load, nearly 11m (1 + 10) for the second, and 52 (1 + 10 + 41) for the third.

Could you upload a reduced data set so I can look at your application?

[Anonymous]

thanks for helping

please find attached

in tab "click source", look at the chart, the first expression is the one i am reffering to

[marcus_malinow]

Hi Nir,

had meant to look at this sooner, but have been busy.

I can't see anything fundamentally wrong there, but really I'd need to see some actual data to be sure.

I appreciate you're probably reluctant to share data, so I'd suggest filtering your data so we can see an example of the issue, then using the 'Reduce Data', 'Keep Possible Values' function. Also you might drop some tables, and scramble your data. If you can do this then upload a document containing some data that would be helpful.

Marcus

[Anonymous]

Hey Marcus,

although it is not what you requested, but i believe it's pointing the issue with a needle:

when using the canonical dates, things doesn't add up,

so i mananged to isolate a discrepancy,

if you look the file attached, you can see the canonical date dimension isn't translating dates properly,

in the attached:

1. SaleDate is the original date

2. Date is the canonical date

while i am using the script above

Love to hear your thoughts,

Nir

[marcus_malinow]

ok, evidently your conversion is not working properly.

Try

date(Saledate, 'MM/DD/YYYY') as Date

[Anonymous]

still not working...