Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

kunkumnaveen
Contributor III

how to group all the fields in to one(vertical column)

Hello All

I got struck with an requirement which i am able to achieve partially .the actual requirement data seem to be like this

table1:

location  score            quater    date(mm/dd/yyyy)

mumbai      100            q1        03/12/2015

mumbai      101            q2        06/20/2015

mumbai      103              q3        09/05/2015

pune          200              q1        03/12/2015

pune          100            q2      06/20/2015

the requirement was to achieve current score ,for this i wrote the expression by using firstsortvalue which look like this

straight table:

dimension :location

expression :  FirstSortedValue(score,-date) ,FirstSortedValue(quater,-date)by this i can achieve current date max score which i got perfect, but now they have extended the requirement a level up

Result:

location        score            quater 

mumbai          103                  q3

pune              100                  q2  

i achieved this task by writing the above expressions.

Now the requirement is like this

table2:(A SAMPLE DATA NOT ACCURATE)

DATE  Quality Governance    Sampling    Lab Analysis    Failure Management    Supplier Management      RM Specifications  score

03/12/2015                  20                          70                  100                150                          10            20                          100

06/20/2015                   10                          10                  10                20                          10            10                            101

  09/05/2015                  30                          20                    20              30                          10            10                            103

03/12/2015                  10                          20                  50                10                        20            10                            200 

06/20/2015                    10                            40                20                  50                      40            10                            100

  So now the requirement is when i press 103(score field)  From the result table it should show be like this

FINAL TABLE

location      Quality Governance Sampling Lab Analysis  Failure Management  Supplier Management  RM Specifications  score 

mumbai            30                          20                    20              30                                    10                          10              103       

Pune                10                            40                20                  50                                  40                    10                    100

Everything is predefine only my concern is how to dispaly them for my client ,i mean shall I display both the tables in same sheet or shall i wirte trigger so that after pressing 103 (SCORE FIELD) Result table it will bring me to Final table which is in sheet 02 ...

is there any chance to group all these fields (Quality Governance Sampling Lab Analysis  Failure Management  Supplier Management  RM Specifications) in a single group and display everything in one table instead of two different tables in pivot

like this

final tableSmiley Sadpivot table)

location       group                 score                 quater

mumbai    +                              103                  q3

pune         +                              100                  q2

thanks

naveen

1 Reply
jonas_rezende
Valued Contributor

Re: how to group all the fields in to one(vertical column)

Hi, naveen kumar.

If you create a composite key in your data model would no solve your problem?  Ex.:

Table1:

Load

autonumber(score& '_' & Date(date))   as [%Composite Key]

location,

score,

quater

from Table1;


Table2:

autonumber(score& '_' & Date(DATE))   as [%Composite Key]

DATE,

Quality,

Governance,

Sampling,

[Lab Analysis],

[Failure Management],

[Supplier Management],

[RM Specifications],

score

from Table2;

Regards,

Jonas Melo.

Community Browser