topic Re: Creating a table with headers and assign the different expressions to each column in QlikView

Creating a table with headers and assign the different expressions to each column

afbraga1 — Wed, 25 Nov 2020 16:16:04 GMT

Hello, I have been trying to create the table in the qvw attached (Table1) and use on each of the columns the correct expression. But I can't seem to do it the correct way. I have tried with 'If clauses' but that would be just bad for performance since this is just an example and the table would be bigger. I have also tried with valuelist (Table2 below) but valuelist makes all combinations between given values (eg: Water only has Quantity and not the rest of the metrics), and that's not what I want. What I am looking to do is something like the Table1 where I can assign the expression to each column. Can anyone help me get around this? Thank you.

Re: Creating a table with headers and assign the different expressions to each column

Anil_Babu_Samineni — Sun, 21 Jan 2018 03:04:07 GMT

Is this you are expecting?

Re: Creating a table with headers and assign the different expressions to each column

afbraga1 — Sun, 21 Jan 2018 10:56:20 GMT

Hello, thanks for looking into this. About your reply, that's not what I want. I want something like my Table 1. There I have all values as 1, because I can't seem to appropriately assign the expressions to the columns. For example, in my qvw, I have the metric #QtyActResin which is the value fall under Resin->Quantity->Actual. I tried to do this with IF:

IF(CostAnlysisH1='Resin' and CostAnalysisH2='Quantity' and CostAnalysisH3='Actual', sum(#QtyActResin), ....) and so on. But this will be terrible for performance because the table is very big. Been trying with pick(match()) but I'm not getting it right.

The Table 2, was an attempt to creating something like the Table1 but with valuelist() dimension, instead of the real dimensions, but didn't manage to do it either.

Re: Creating a table with headers and assign the different expressions to each column

Anil_Babu_Samineni — Sun, 21 Jan 2018 11:31:23 GMT

The only difference i can think here is, Using set analysis to achieve and make better job.

Sum({<CostAnalysisH1 = {'Resin'}, CostAnalysisH2 = {'Quantity'}, CostAnalysisH3 = {'Actual'}>} #QtyActResin)

Re: Creating a table with headers and assign the different expressions to each column

afbraga1 — Sun, 21 Jan 2018 11:38:52 GMT

Tried your suggestion, but it ends putting 0 values for the other expression that is missing on each column.

Re: Creating a table with headers and assign the different expressions to each column

Anil_Babu_Samineni — Sun, 21 Jan 2018 11:48:16 GMT

I just copied that expression in expression section. Due to we added only three filters so it showing as expected

Re: Creating a table with headers and assign the different expressions to each column

Anil_Babu_Samineni — Sun, 21 Jan 2018 11:49:12 GMT

And What you shown image is correct, This calculates Row level only. So, What are you trying to add here?

Re: Creating a table with headers and assign the different expressions to each column

afbraga1 — Sun, 21 Jan 2018 11:55:24 GMT

What I want is something that ends up looking like this excel example.

The expression you provided while it works alone, when I start to add more expressions to fill the other columns

they will start to put 0 values on the other columns that they don't fit as my example in the previous post, and that is not what I want.

Re: Creating a table with headers and assign the different expressions to each column

afbraga1 — Sun, 21 Jan 2018 12:19:54 GMT

I think I might have done it with the valuelist() on my Table 2. But now I wanted to sort it like the Table 1. Any idea how I might achieve this, been trying but nothing is working.

Re: Creating a table with headers and assign the different expressions to each column

sunny_talwar — Sun, 21 Jan 2018 15:15:30 GMT

Since you have already created the dimension in the script... why can't you use this?

Dimensions

Company

CostAnalysisH1

CostAnalysisH2

CostAnalysisH3

Expression

Pick(wildmatch(CostAnalysisH1,$(vCostAnalysisH1)),

Pick(wildmatch(CostAnalysisH2,$(vCostAnalysisH2)),

Pick(wildmatch(CostAnalysisH3,$(vCostAnalysisH3)),1),Pick(wildmatch(CostAnalysisH3,$(vCostAnalysisH3)),2,3)),

Pick(wildmatch(CostAnalysisH2,$(vCostAnalysisH2)),

Pick(wildmatch(CostAnalysisH3,$(vCostAnalysisH3)),4)),

Pick(wildmatch(CostAnalysisH2,$(vCostAnalysisH2)),

Pick(wildmatch(CostAnalysisH3,$(vCostAnalysisH3)),5),Pick(wildmatch(CostAnalysisH3,$(vCostAnalysisH3)),6,7))

)

Re: Creating a table with headers and assign the different expressions to each column

afbraga1 — Sun, 21 Jan 2018 15:33:39 GMT

That's great, thank you Sunny! One last question, why is that when I select the dimension its values changes? Is there a way to maintain the correct values when filtering the table? Thank you.

Re: Creating a table with headers and assign the different expressions to each column

sunny_talwar — Sun, 21 Jan 2018 15:39:03 GMT

Check now

Expression

Pick(wildmatch(Only({<CostAnalysisH1, CostAnalysisH2, CostAnalysisH3>}CostAnalysisH1),$(vCostAnalysisH1)),

Pick(wildmatch(Only({<CostAnalysisH1, CostAnalysisH2, CostAnalysisH3>}CostAnalysisH2),$(vCostAnalysisH2)),

Pick(wildmatch(Only({<CostAnalysisH1, CostAnalysisH2, CostAnalysisH3>}CostAnalysisH3),$(vCostAnalysisH3)),1),Pick(wildmatch(Only({<CostAnalysisH1, CostAnalysisH2, CostAnalysisH3>}CostAnalysisH3),$(vCostAnalysisH3)),2,3)),

Pick(wildmatch(Only({<CostAnalysisH1, CostAnalysisH2, CostAnalysisH3>}CostAnalysisH2),$(vCostAnalysisH2)),

Pick(wildmatch(Only({<CostAnalysisH1, CostAnalysisH2, CostAnalysisH3>}CostAnalysisH3),$(vCostAnalysisH3)),4)),

Pick(wildmatch(Only({<CostAnalysisH1, CostAnalysisH2, CostAnalysisH3>}CostAnalysisH2),$(vCostAnalysisH2)),

Pick(wildmatch(Only({<CostAnalysisH1, CostAnalysisH2, CostAnalysisH3>}CostAnalysisH3),$(vCostAnalysisH3)),5),Pick(wildmatch(Only({<CostAnalysisH1, CostAnalysisH2, CostAnalysisH3>}CostAnalysisH3),$(vCostAnalysisH3)),6,7))

)

Variables

=Concat({<CostAnalysisH1, CostAnalysisH2, CostAnalysisH3>}DISTINCT(chr(39)&CostAnalysisH1&chr(39)),',')

=Concat({<CostAnalysisH1, CostAnalysisH2, CostAnalysisH3>}DISTINCT(chr(39)&CostAnalysisH2&chr(39)),',')

=Concat({<CostAnalysisH1, CostAnalysisH2, CostAnalysisH3>}DISTINCT(chr(39)&CostAnalysisH3&chr(39)),',')

Re: Creating a table with headers and assign the different expressions to each column

afbraga1 — Sun, 21 Jan 2018 15:44:27 GMT

Ok, I understand what you have done, but what if I wanted to filter it just to show Wood or Resin or Water, can it be one? I ask this because the table I'm looking to create is quite big, so I wouldn't want to show always all columns at same time.

Re: Creating a table with headers and assign the different expressions to each column

sunny_talwar — Sun, 21 Jan 2018 16:18:04 GMT

My bad, I thought you wanted to always see the H1's, H2's, and H3's... check now

Re: Creating a table with headers and assign the different expressions to each column

afbraga1 — Sun, 21 Jan 2018 18:12:53 GMT

Ok, this is awesome exactly what I was looking for. But what did you do exactly to achieve that? I copied everything as an example to another qvw file and it isn't working anymore strangely. Only in the one you provided, and I can't seem to understand why.

Re: Creating a table with headers and assign the different expressions to each column

sunny_talwar — Sun, 21 Jan 2018 18:16:04 GMT

Here you are

Dimensions

Company

CostAnalysisH1

CostAnalysisH2

CostAnalysisH3

Expressions

Pick(wildmatch(Only(CostAnalysisH1),$(vCostAnalysisH1)),

Pick(wildmatch(Only(CostAnalysisH2),$(vCostAnalysisH2)),

Pick(wildmatch(Only(CostAnalysisH3),$(vCostAnalysisH3)),1),Pick(wildmatch(Only(CostAnalysisH3),$(vCostAnalysisH3)),2,3)),

Pick(wildmatch(Only(CostAnalysisH2),$(vCostAnalysisH2)),

Pick(wildmatch(Only(CostAnalysisH3),$(vCostAnalysisH3)),4)),

Pick(wildmatch(Only(CostAnalysisH2),$(vCostAnalysisH2)),

Pick(wildmatch(Only(CostAnalysisH3),$(vCostAnalysisH3)),5),Pick(wildmatch(Only(CostAnalysisH3),$(vCostAnalysisH3)),6,7))

)

Variables

=Concat({<CostAnalysisH1, CostAnalysisH2, CostAnalysisH3>}DISTINCT(chr(39)&CostAnalysisH1&chr(39)),',')

=Concat({<CostAnalysisH1, CostAnalysisH2, CostAnalysisH3>}DISTINCT(chr(39)&CostAnalysisH3&chr(39)),',')

Re: Creating a table with headers and assign the different expressions to each column

sunny_talwar — Sun, 21 Jan 2018 18:16:38 GMT

Basically ignore selection in variable and don't ignore selections from your expression

Re: Creating a table with headers and assign the different expressions to each column

afbraga1 — Sun, 21 Jan 2018 18:24:07 GMT

Ah that was it, I didn't realize you added it on the variable overview Thanks for amazing help Sunny!!

Re: Creating a table with headers and assign the different expressions to each column

sunny_talwar — Sun, 21 Jan 2018 18:24:55 GMT

Not a problem, I am glad I was able to help....

Please close the thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny