topic Re: Avoiding a loop in the data model in QlikView

Avoiding a loop in the data model

Tue, 07 Feb 2012 15:04:43 GMT

Hello,

I decided to ask the community for this one, i am all out of ideas:

I have three dimension tables and a fact. All dimensions are dates, and the fact has three date columns and one ID.

First dimension is the EnteredDate, the second is SentDate and the third is ReceivedDate.

How can I achieve a graph with three different metrics(expressions) and show in time the count of ID's for each dimension?

Entered:

ID Month

1 jan

2 jan

3 feb

4 feb

5 mar

Sent:

ID Month

1 jan

2 feb

4 feb

5 mar

Received:

ID Month

1 mar

4 mar

Desired result:

Month Entered Sent Received

Jan 2 1 0

Feb 2 2 0

Mar 1 1 2

Thank you!

Re: Avoiding a loop in the data model

Anonymous — Tue, 07 Feb 2012 16:05:01 GMT

If you can live with it, I would recommend the following table structure:

LOAD * Inline [

Month,ID,Status

jan,1,Entered

...

jan,1,Sent

...

mar,1,Received

...

];

Where ... represents data I unfortunately don't have time to type in. So, "Status" is a new field which tells you which table the data originates from.

Then you can have Month as your Dimension, and your straight table expressions would be

count({$<Status={"Entered"}>} ID)

count({$<Status={"Sent"}>} ID)

count({$<Status={"Received"}>} ID)

Hopefully that made sense

Re: Avoiding a loop in the data model

lironbaram — Tue, 07 Feb 2012 19:58:39 GMT

hi check the attach example

Re: Avoiding a loop in the data model

Wed, 08 Feb 2012 08:05:52 GMT

hi all,

thank you for the help, but i am not able to load the data in this way.

I have an operational database (no aggregates available) with one big table that has:

ID .... DateEntered DateSent DateReceived ...

I created, based on this, a start schema with the ID as a fact (and other columns) and with KeyDates for each date dimensions. See attached screenshot. I want to be able to move from a month level to a quarter or year, etc. so i need those tables as they are now.

The only way i can think of is creating a separateisland, but i'm not sure how to do that in my case, because i think i need a separate calendar table that can join with duplicates of the three time dimensions. Or not?!!?

Thank you!

Avoiding a loop in the data model

vijay_iitkgp — Wed, 08 Feb 2012 08:12:15 GMT

Hi,

You can try this:

Load

Id,

DateEntered as Date,

'Entered' as Flag

From T1;

Concatenate

Load

Id,

DateSent as Date

'Sent' as Flag

From T1;

Concatenate

Load

Id,

DateReceived as Date

'Received' as Flag

From T1;

Hope this will help

Re: Avoiding a loop in the data model

Wed, 08 Feb 2012 08:30:46 GMT

Hi,

Please look at the attached file. It solves your problem.

Thanks,

Niranjan M

Re: Avoiding a loop in the data model

Wed, 08 Feb 2012 10:07:48 GMT

thank you! i forgot the power of Concatenation