topic Staring blind on creating the expression ... HELP! in QlikView

Staring blind on creating the expression ... HELP!

Anonymous — Tue, 15 Jun 2010 14:33:15 GMT

I think I'm staring blind at this ... maybe someone can help me find the light ?

I have a table with sessions:

SESSION_ID, SERVICE_ID, ........ , INTERVAL
1, 123, .... , 1
2, 123, .... , 1
3, 124, .... , 28
4, 123, .... , 30
5, 125, .... , 1
6, 123, .... , 30
7, 126, .... , 30

I'm looking for something to show the number of SERVICE_ID's for a certain INTERVAL.

The count should be something with the DISTINCT SERVICE_ID and the max(SESSION_ID) ...

Result should be something like:

1 - 1x
28 - 1x
30 - 2x

I would like the INTERVAL to be a dimension ...

Can anybody help my on the way with the expression??

many thanks!!

Anita

Staring blind on creating the expression ... HELP!

Oleg_Troyansky — Tue, 15 Jun 2010 17:24:45 GMT

Anita,

I think you need to clarify to us and to yourself, what exactly are you after? For example:


A Fuchten wrote:I'm looking for something to show the number of SERVICE_ID's for a certain INTERVAL.

----------------------------

If INTERVAL is a Dimension, then COUNT(DISTINCT SERVICE_ID) would answer this question.

On the other side:


A Fuchten wrote:The count should be something with the DISTINCT SERVICE_ID and the max(SESSION_ID) ...

----------------------------

not sure what you mean here... What's the relation to the Session ID ?


A Fuchten wrote:
Result should be something like:
1 - 1x
28 - 1x
30 - 2x

------------------------------

I counted 2 distinct Sessions with Interval = 1. Am I counting wrongly?

please clarify your needs...

Staring blind on creating the expression ... HELP!

johnw — Tue, 15 Jun 2010 21:25:34 GMT

If I understood the question:

[Data]:
LOAD * INLINE [
SESSION_ID, SERVICE_ID, INTERVAL
1, 123, 1
2, 123, 1
3, 124, 28
4, 123, 30
5, 125, 1
6, 123, 30
7, 126, 30
];
LEFT JOIN ([Data])
LOAD *
,if(SERVICE_ID<>previous(SERVICE_ID),1) as Max?
RESIDENT [Data]
ORDER BY SERVICE_ID,SESSION_ID DESC
;

Dimension = INTERVAL
Expression = count({<Max?={1}>} distinct SERVICE_ID)

Staring blind on creating the expression ... HELP!

johnw — Tue, 15 Jun 2010 21:36:11 GMT

Non-script approach if you want the max(SESSION_ID) to be responsive to selections:

Dimension = INTERVAL
Expression = count({<SESSION_ID={$(=concat(aggr(max(SESSION_ID),SERVICE_ID),','))}>} distinct SERVICE_ID)

Not sure about the performance, though, and it might crash if your data set is large enough. Might be some other approach that would work better.

Staring blind on creating the expression ... HELP!

Anonymous — Thu, 17 Jun 2010 08:58:43 GMT

Looks that this is what i'm looking for ...

Will try this out later today!!