Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Contributor II

pivot table - expression

Hi Everyone,

I have data that looks something like this

                   Compliant          In office          No time booked

Employee

EMP1                                                               1

EMP2                                     1

EMP3            1

.

.

The dimension is employee and Aggr(FirstSortedValue([COMPLIANCE REASON] , -[COMPLIANCE PRIORITY]), EMP_ID)

and the expression COUNT(DISTINCT EMP_ID)


This works fine. However there is time booked by these employees that i want to capture. SUM([TIME]) leads to incorrect values. It adds up all time regardless of the compliant values.

what i have

                   Compliant          In office          No time booked

Employee

EMP1                                                               320

EMP2                                     320

EMP3            320

what i need

                   Compliant          In office          No time booked

Employee

EMP1                                                               7

EMP2                                     10

EMP3            320

The above values are because in the 320 hrs EMP1 and EMP2 have been non compliant for 7,10 hrs only. Any ideas ?

13 Replies
Highlighted

Re: pivot table - expression

Would it be possible to share a sample to look at the issue?

Highlighted
Honored Contributor II

Re: pivot table - expression

a sample please.

Learning never stops.
Highlighted
Contributor II

Re: pivot table - expression

apologies.. i cannot upload any files.

Highlighted

Re: pivot table - expression

Can you at least provide mocked up data?

Highlighted
New Contributor II

Re: pivot table - expression

Are you joing multiple tables to create this table?

Highlighted
Contributor II

Re: pivot table - expression

hope this helps.

EMP_ID      WEEKID     PROJ_ID     [TIME TO BE BOOKED]      [TIME BOOKED]     COMPLAINCE_ID

EMP1          1                           1             7                                             7                             1

EMP1          2                           1             7                                             -                              3

EMP2          1                           3             10                                             -                              3

EMP2          1                           6             -                                             10                               2   

EMP2          2                           3              10                                           10                             1

EMP3          1                          4               6                                             6                              1

EMP3          1                          5               2                                             2                              1

EMP3          2                          4               6                                             6                              1

EMP3          2                          5               2                                             2                              1


Compliance table :

LOAD * INLINE

[

COMPLIANCE_ID, COMPLIANCE, COMPLIANCE REASON, COMPLIANCE PRIORITY

1, 1, COMPLIANT, 1

2, 0, IN OFFICE, 2

3, 0,  NO TIME BOOKED, 3

];



Highlighted

Re: pivot table - expression

In the above sample, what would Sum(TIME) be? Sum([TIME TO BE BOOKED]) or Sum([TIME BOOKED])? Also, what would the correct output for the expression we have to use?

Highlighted
Contributor II

Re: pivot table - expression

Time is minimum of the two columns "Time to be booked" and "time booked"

the correct output for the above sample data would be :

                   Compliant          In office          No time booked

Employee

EMP1                                                               7

EMP2                                     10

EMP3            16

Highlighted
Contributor II

Re: pivot table - expression

yes, it derives data from fact and dimensions table