topic Re: Pivot Table in QlikView

Pivot Table

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

Please find the below table and let me how can i achieve the my expression

Country	City	Count	Expression?
India	Hyd	1	Chennai+Mumbai+Delhi+New Jersey (14)
India	Chennai	2	Hyd+Mumbai+Delhi+New Jersey (13)
India	Mumbai	3	Hyd+Chennai+Delhi+New Jersey (12)
India	Delhi	4	Hyd+Chennai+Mumbai+New Jersey (11)
USA	New Jersey	5	Hyd+Chennai+Mumbai+Delhi (10)

If you observe my expression,always i have to exclude respective city for that row.

MK9885 — Thu, 08 Dec 2016 14:43:56 GMT

I do not think it is possible to write such an expression with it gives you

+ with strings and number at the end.

It is possible to show one City per column but not a mix of all with +

If you really want that then use your last Column into Inline table or add a column next to count as you've done above, without numeric values.

ASFAIK it can;t be done, maybe someone with expertise level can guide you well?

rupamjyotidas — Thu, 08 Dec 2016 14:48:59 GMT

Simplest will be this

Sum(TOTAL Count) - Sum(Count)

trdandamudi — Thu, 08 Dec 2016 17:27:47 GMT

Please see the attachment for the solution and hope this helps..

Anil_Babu_Samineni — Fri, 09 Dec 2016 00:51:15 GMT

One more way this by using Set Analysis, But, This is not make sense any how. Hope getting some idea

=If(Count = 1, Sum({<City = {'Chennai','Delhi','Mumbai','New jersey'}>} TOTAL Count),

If(Count = 2, Sum({<City = {'Delhi','Hyd','Mumbai','New jersey'}>} TOTAL Count),

If(Count = 3, Sum({<City = {'Chennai','Delhi','Hyd','New jersey'}>} TOTAL Count),

If(Count = 4, Sum({<City = {'Chennai','Hyd','Mumbai','New jersey'}>} TOTAL Count),

If(Count = 5, Sum({<City = {'Chennai','Delhi','Hyd','Mumbai'}>} TOTAL Count)

)