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

[afbraga1]

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.

[afbraga1]

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.

[sunny_talwar]

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)),',')

[afbraga1]

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.

[sunny_talwar]

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

[afbraga1]

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.

[sunny_talwar · Accepted solution]

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)),',')

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

[sunny_talwar]

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

[afbraga1]

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

[sunny_talwar]

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