Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Want to see what's currently in public preview? We've pulled it all together in one place. Check it out here
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2016-10-04 12:42 PM

drill down - table

Hey everyone, how is it going?

I have an issue, let me show you: My data base is something like that:

    

ProductDateRM 1RM 2RM 3RM Total
A01/01/20161056075
B01/01/201615105580
C01/01/201620155590
D01/01/2016252060105
A02/01/2016302585140
B02/01/20163530115180
C02/01/20164035150225
D02/01/20164540190275
A03/01/20165045235330

I want to make a pivot table, where I can show by product, how much was spent of raw material and if the users wants, they can open by kind of RM (RM 1, RM2 and RM3) .

I already appreciate the attention.


Kind regards, Luiz Bisco



Labels (1)
Labels
3,756 Views
0 Likes
1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III
‎2016-10-06 11:02 AM

In response to

Use the below expression

and check partial sums for the first two dimensions

=if(Dimensionality()=2,Pick(MATCH(Process,'Process 1','Process 2','Process 3','Process 4','Process 5','Process 6','Process 7','Process 8'),
sum([Process 1])
,sum([Process 2])
,sum([Process 3])
,sum([Process 4])
,sum([Process 5])
,sum([Process 6])
,sum([Process 7])
,sum([Process 8])
)


,if(Dimensionality()=1,

sum([Process 1])+sum([Process 2])+sum([Process 3])+sum([Process 4])+sum([Process 5])+sum([Process 6])+sum([Process 7])+sum([Process 8])

, if(Dimensionality()=0,sum(TOTAL [Process 1])+sum(TOTAL [Process 2])+sum(TOTAL [Process 3])+sum(TOTAL [Process 4])+sum(TOTAL [Process 5])+sum(TOTAL [Process 6])+sum(TOTAL [Process 7])+sum(TOTAL [Process 8]))

)
)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

fake_pivot.qvw
3,513 Views
0 Likes
14 Replies
sunny_talwar
MVP
MVP
‎2016-10-04 12:48 PM

Like this?

Capture.PNG

If this looks good, then you will have to use CrossTable to transform your data

Table:

CrossTable (RM, Value, 2)

LOAD Product,

    Date,

    [RM 1],

    [RM 2],

    [RM 3]

FROM

[https://community.qlik.com/thread/235208]

(html, codepage is 1252, embedded labels, table is @1);

Community_235208.qvw
2,520 Views
Not applicable
‎2016-10-04 02:59 PM
Author

In response to sunny_talwar

Sunny, there's a problem. That data is kind of big. I have a table with 96 fields, 43 are dimensions and the others are capeble of calc.

2,520 Views
0 Likes
vinieme12
Champion III
Champion III
‎2016-10-04 03:29 PM

Well you can maintain your data structure and FAKE a pivot like this, with a mapping table

fake_pivot.JPG

////////////////////////////////////////////////////////////////////////////////////

Dimension

Product

RM

Expression:

pick(Match(RM,'RM 1','RM 2','RM 3'),

  SUM([RM 1]) , SUM([RM 2]) , SUM([RM 3]))

////////////////////////////////////////////////////////////////////////////////////

Fact:

LOAD * INLINE [

Product,Date,RM 1,RM 2,RM 3,RM Total

A,42370,10,5,60,75

B,42370,15,10,55,80

C,42370,20,15,55,90

D,42370,25,20,60,105

A,42371,30,25,85,140

B,42371,35,30,115,180

C,42371,40,35,150,225

D,42371,45,40,190,275

A,42372,50,45,235,330

];

Mappingtable:

LOAD * INLINE [

Product,RM

A,RM 1

B,RM 1

C,RM 1

D,RM 1

A,RM 2

B,RM 2

C,RM 2

D,RM 3

A,RM 3

B,RM 3

C,RM 3

D,RM 3

];

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
2,520 Views
Not applicable
‎2016-10-05 10:31 AM
Author

In response to vinieme12

I did on your way, it works, but I can't 'contract' the dimension.

2,520 Views
0 Likes
vinieme12
Champion III
Champion III
‎2016-10-05 11:06 AM

In response to

I did this in QV12 , what version of QV are you using?

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
2,520 Views
Not applicable
‎2016-10-05 11:09 AM
Author

In response to vinieme12

11.2, this data is the real scenario:

   

DateMachineProcess 1 Process 2Process 3Process 4Process 5Process 6Process 7Process 8 Production 
01/01/2016MACHINE 1               200                  20                  30                  77                  50                  60                  70                  72                  579
01/01/2016MACHINE 2                 99                  30                200                  10                  70                  82                  491
01/01/2016MACHINE 3                 10                  20                  30                  40                  50                  60                  70                  40                  320
01/01/2016MACHINE 4                 66                  60                  70                  65                  261
01/01/2016MACHINE 5                 10                  20                  30                  40                  50                    2                  70                  32                  254
01/01/2016MACHINE 6                 10                  55                  30                  40                  44                  36                  215
01/02/2016MACHINE 1               308                  31                  46                118                  77                  92                108                111                  891
01/02/2016MACHINE 2                  -                  152                  46                   -                  308                  15                108                  90                  719
01/02/2016MACHINE 3                 15                  31                  46                  62                  77                  92                108                  62                  492
01/02/2016MACHINE 4                  -                  102                   -                     -                     -                    92                108                  43                  345
01/02/2016MACHINE 5                 15                  31                  46                  62                  77                    3                108                  49                  390
01/02/2016MACHINE 6                 15                  85                  46                  62                   -                     -                    68                  39                  315
01/03/2016MACHINE 1               473                  47                  71                182                118                142                166                171               1.371
01/03/2016MACHINE 2                  -                  234                  71                   -                  473                  24                166                138               1.106
01/03/2016MACHINE 3                 24                  47                  71                  95                118                142                166                  95                  757
01/03/2016MACHINE 4                  -                  156                   -                     -                     -                  142                166                  66                  530
01/03/2016MACHINE 5                 24                  47                  71                  95                118                    5                166                  75                  601
01/03/2016MACHINE 6                 24                130                  71                  95                   -                     -                  104                  61                  484
2,520 Views
0 Likes
vinieme12
Champion III
Champion III
‎2016-10-05 11:16 AM

In response to

i'll try in 11.2 and let you know

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
2,520 Views
vinieme12
Champion III
Champion III
‎2016-10-05 11:44 AM

In response to

Make sure you have the below UN-Checked

1) Always fully expanded

2) Suppress Missing Values

Attaching app for reference, made in QV 11.2

fake_pivot.JPG

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
fake_pivot.xlsx
fake_pivot.qvw
2,520 Views
Not applicable
‎2016-10-05 12:12 PM
Author

In response to vinieme12

that's the problem, I have to contratc, it can't be fully expanded all the time. :S

2,520 Views
0 Likes