Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Avoiding a loop in the data model

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!

1 Solution

Accepted Solutions
vijay_iitkgp
Valued Contributor

Avoiding a loop in the data model

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

6 Replies
lf_fk_bkk
New Contributor III

Re: Avoiding a loop in the data model

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

lironbaram
Honored Contributor II

Re: Avoiding a loop in the data model

hi check the attach example

Not applicable

Re: Avoiding a loop in the data model

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!

qv_forum.jpg

vijay_iitkgp
Valued Contributor

Avoiding a loop in the data model

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

Not applicable

Re: Avoiding a loop in the data model

Hi,

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

Thanks,

Niranjan M

Not applicable

Re: Avoiding a loop in the data model

thank you! i forgot the power of Concatenation

Community Browser