topic Re: Different expression each row in a table in QlikView

Different expression each row in a table

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

Hello

I have a problem I can't seem to figure out..

I need to create a table with different expressions in each row and custom dimensions.

Product
Senior
Commercial
Medi-Cal

Product 2
SNPD
CAL MEDICONNECT
POS
EXCHANGE
SNPC
LIC
SALUD
OTHER
TANF
ALLIANCE
LTC
MCE

Region
DO
SF
SL
WH

I am calculating number of admits(0-2) per 1,000 and bed days(1-31) per 1,000..

for example, below is admit/1,000 expression

=SUM({<AdIPType={'ACUTE'}>} ADMIT)/COUNT(DISTINCT DateofService)/[AVG MBRSHP]*12000

but i need a very specific table as below

Dimension	Bed days/K	Admits/K
Product= Senior, Product 2 = SNPD, SNPC (Actual label for the colum will be Senior with SNPD,SNPC)
Product = Medi-Cal
SF Region Medi-Cal
Product = Commercial, Product2 = EXCHANGE, Region= DO

How would i go about doing this? So basically an end user will only pick time frame filter (YEAR, Quarter, etc). I need those dimensions fixed and bed days and admits to calculate according to the specific dimension in each row. Also need this in a table so it can be exported into excel.

Any help is appreciated.

Thanks

Re: Different expression each row in a table

juraj_misina — Fri, 28 Sep 2018 08:54:01 GMT

Hi Caleb,

I usually try to prepare my data in the data model as much as possible to fit my needs, but this seems to be a very specific requirement. I such cases I do a nasty thing and use ValueList() function. You can create a calculated dimension (replace Dim1 etc with your dimension values 'Product = Senior...'):

ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4')

And then an expression:

Pick(Match(ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4'), 'Dim1', 'Dim2', 'Dim3', 'Dim4'),

Expression for Dim1,

Expression for Dim2,

Expression for Dim3,

Expression for Dim4)

Be careful though, my exprerience is that these kind of requirements tend to be superspecific and rather complex, so it might not perform well on large datasets.

Hope this helps

Juraj

Re: Different expression each row in a table

prieper — Fri, 28 Sep 2018 09:20:37 GMT

a bit more performant is to create a dataisland with DUAL:
Dimensions:

LOAD DUAL(Dim, Sort) AS Dim INLINE [Dim, Sort

Dimension1, 1

Dimension2, 2

Dimension3, 3];

Choose then "Dim" as Dimension

Expression then might be

PICK(Dim,

// Calculation 1

SUM({<Product = {"Senior"}>} Amount),

// Calculation 2

SUM({<Product = {"Medi"}>} Amount),

// Calculation 3

SUM({<Product = {"Senior", "Medi"}>} Amount))

Have not noticed slow performance,

however can underline Juraj's comment, that such report is super-inflexible.

Another advantage is that within the Island-table you may also fix some formatting like Bold or Background-colours.

Peter

Re: Different expression each row in a table

hkkwon89 — Fri, 28 Sep 2018 22:28:13 GMT

Hi Peter,

Does pick function then choose the expression in the sort order?

Re: Different expression each row in a table

juraj_misina — Sun, 30 Sep 2018 11:38:24 GMT

Basically yes. The first argument of pick function is a positive integer based on which Pick chooses wich expression to return/evaluate. In Peter's example Dim field has both string and numeric value, so you can use it as an input for Pick() to return corresponding calculation.

Re: Different expression each row in a table

hkkwon89 — Mon, 01 Oct 2018 16:03:02 GMT

Thank you so much for your help!

Re: Different expression each row in a table

Anonymous — Wed, 14 Nov 2018 16:23:20 GMT

Hello,

just jumping in on this, I tried your solution Juraj_misina but there seems to be a subtelty I am missing...

I tried something like this, but the result is wrong for Dim3. Any insight on this?

Pick(Match(ValueList('Dim1', 'Dim2', 'Dim3'), 'Dim1', 'Dim2', 'Dim3'),

sum(a), //Expression for Dim1

sum(b), //Expression for Dim2

sum(a)/sum(b)) //Expression for Dim3