topic Re: Challange with pivot table in QlikView

Challange with pivot table

Wed, 04 Nov 2015 16:52:19 GMT

Hi all,

I've some challange with get the information I want in a pivot table.

First I've som dimensions:

- CC

- EmpGroup

- TimeType

- EmpID

Then I've a expression:

if(IsNull(STARTTime)= -1, Absence, Timestamp(STARTTime,'hh:mm')) so I get STARTTime and if there are no STARTTime I will have the Absence.

This doesen't work that well so I change the Expression like this:

sum(aggr(if(IsNull(STARTTime)= -1, Absence, Timestamp(STARTTime,'hh:mm')), STARTTime, Absence, CC,EmpGroup,TimeType, EmpID))

This works much better so I get all the STARTTime but I will not get any Absence.

Any one that have a suggestion?

Re: Challange with pivot table

Gysbert_Wassenaar — Wed, 04 Nov 2015 17:16:14 GMT

Something like count(Alt(STARTTime, Absence)). Though I have no idea what you're trying to calculate.

Re: Challange with pivot table

Qrishna — Wed, 04 Nov 2015 17:29:13 GMT

Try

if(len(STARTTime)= 0, Absence, Timestamp(STARTTime,'hh:mm'))

this returns Timestamp(STARTTime,'hh:mm') when len(STARTTime) is not zero else 'Absence'

Re: Challange with pivot table

swuehl — Wed, 04 Nov 2015 17:37:40 GMT

For every combination of dimension values, is it guaranteed that there is only one unique value of STARTTime or Absence? I assume it's not, so you need to consider using

a) aggregation functions!

Use Aggregation Functions!

b) add dimensions, so that it's guaranteed that there is only() unique value per combination of dimensional values.

You are potentially summing different STARTTimes in your second expression, is this really what you want (summing times? or do you want to sum intervals? Or do you just show a single value?)

Re: Challange with pivot table

Thu, 05 Nov 2015 07:32:53 GMT

Yes it should be only one unique single value of STARTTime or Absence.

I just want to show a single value, if there are any STARTTime show that otherwise Absence.

Re: Challange with pivot table

Thu, 05 Nov 2015 07:35:53 GMT

It works like my fortst expression: if(IsNull(STARTTime)= -1, Absence, Timestamp(STARTTime,'hh:mm'))

So unfortunately it don't give me the right values.

Re: Challange with pivot table

Thu, 05 Nov 2015 07:46:03 GMT

I just want to show value, if there are any STARTTime show that otherwise Absence.

The attached picture does only show STARTTime.

Re: Challange with pivot table

swuehl — Thu, 05 Nov 2015 11:09:56 GMT

What if you expand all dimensions? I think with collapsed dimensions, you probably don't have unique values.