Count with different columns

[paulwalker]

Hi Community,

below is my table (after transformation my data looks like below)

LOAD * INLINE [
ID, AREA, Status_1, Status_V1, Status_2, Status_V2
100, Qlik Sense, Qlik Sense, 80, QlikView, 20
101, QlikView, QlikView, 100, ,
102, QlikView, QlikView, 100, ,
103, QlikView, QlikView, 100, ,
104, QlikView, QlikView, 100, ,
105, QlikView, QlikView, 100, ,
106, QlikView, QlikView, 100, ,
107, QlikView, QlikView, 100, ,
];

My count should be club with Status_1 and Status_2

QlikView Count should be 8 (which I have highlighted) and Qlik Sense should be 1

and same way sum QlikView=720 and Qlik Sense=80

Please note should not be hard code in set expression, I have many values  not only QlikView and Qlik Sense.

@tresesco, @MayilVahanan,  @Kushal_Chawda, @marcus_sommer, @stevedark 

and Experts - Can you please take a look!

[marcus_sommer · Accepted solution]

I would merge the status and the value fields into a single field each, for example like:

t: load ID, Area, Status1 as Status, V1 as V, 1 as Source from Source; concatenate(t)
    load ID, Area, Status2 as Status, V2 as V, 2 as Source from Source;

If there are more status-fields it might be more practically to load this logic with one or two crosstable loads.