topic Re: Set analysis to equal a calculated concatenated value in QlikView

Set analysis to equal a calculated concatenated value

Dayna — Wed, 08 Mar 2017 17:35:00 GMT

Hello All!

This might sound simple, but I'm struggling with it... I'm trying to write a set analysis statement that does a calculation if a combined value equals another. So, for example

I have some fields to get prices:

Area	Price
ABC1	1
ABC2	2
ABC3	3

I also have another set of information that has the area, but the leading number changes based on a calculation to work out the quantity.

AreaGroup	Number of Items
ABC	1
ABC	2

To get the number of items, this is the calculation I'm doing:

ceil(sum(if(ad_zone =1, (idh_qty_inv/idh_conv), ((idh_qty_inv/idh_conv)/2))))

What I need to do, is, if AreaGroup & Number if Items = Area, then use the price. However, as I don't have both of these fields exposed in the table I need to do a set statement. I was thinking something like:

sum({<Area={$(=AreaGroup&(ceil(sum(if(ad_zone =1, (idh_qty_inv/idh_conv), ((idh_qty_inv/idh_conv)/2)))))&'`',',') }>} 1)

But this isn't working... Can anyone suggest any method to use a concatenated field with a calculation to get the resulting number, please?

Many thanks!!

Dayna

Re: Set analysis to equal a calculated concatenated value

Anonymous — Wed, 08 Mar 2017 18:06:42 GMT

I don't understand what you need exactly. Please provide an example within an Excel.

But the formula I marked red will be calculated without any dimension hierarchy.

A set expression is similar to a selection within a listbox. So this can't work.

sum({<Area={$(=AreaGroup&(ceil(sum(if(ad_zone =1, (idh_qty_inv/idh_conv), ((idh_qty_inv/idh_conv)/2)))))&'`',',') }>} 1)

Re: Set analysis to equal a calculated concatenated value

vishsaggi — Wed, 08 Mar 2017 19:31:26 GMT

Can you share a sample app to look into? Are these two variable or fields? -> idh_qty_inv , idh_conv ?

Quite did not get what that line underlined in the below expr does?

sum({<Area={$(=AreaGroup&(ceil(sum(if(ad_zone =1, (idh_qty_inv/idh_conv), ((idh_qty_inv/idh_conv)/2)))))&'`',',') }>} 1)

Re: Set analysis to equal a calculated concatenated value

Dayna — Thu, 09 Mar 2017 09:36:30 GMT

Hi All,

I've modified the set analysis statement slightly and included in my copy attached, it's nearly there, but the expression is bringing back the same value for all rows whereas it should bring back the value depending on the combined value (i.e. SHAWGB5, SHAWGB6, etc..).

Hopefully the attached makes sense!

Many thanks for your help,

Dayna

Re: Set analysis to equal a calculated concatenated value

vishsaggi — Thu, 09 Mar 2017 14:24:54 GMT

Would you mind telling us the exact output for some values in that table?

Re: Set analysis to equal a calculated concatenated value

Dayna — Thu, 09 Mar 2017 14:27:44 GMT

Which values do you mean sorry? The concatenated string that I'm comparing against or the cost I want to display?

Re: Set analysis to equal a calculated concatenated value

vishsaggi — Thu, 09 Mar 2017 14:28:51 GMT

Your expected output i mean. I can see three expressions there.

Re: Set analysis to equal a calculated concatenated value

Dayna — Thu, 09 Mar 2017 14:39:54 GMT

Ah I see! For this expression:

sum({<PalletSpaceCost_CombinedValue={$(=PalletSpaceCost_Area&(ceil(sum(if(ad_zone =1, (idh_qty_inv/idh_conv), ((idh_qty_inv/idh_conv)/2)))))) }>} PalletSpaceCost_Cost)

Which has the inputs of:

SHAWGBWN2

SHAWGBWN1

SHAWGBWN4

SHAWGBWN2

SHAWGBWN1

So I'd expect:

PalletSpaceCost_CombinedValue	PalletSpaceCost_Cost
SHAWGBWN2	83.00
SHAWGBWN1	58.00
SHAWGBWN4	110.00
SHAWGBWN2	83.00
SHAWGBWN1	58.00
SHAWGBWN1	58.00

Hope that helps!!!

Re: Set analysis to equal a calculated concatenated value

vishsaggi — Thu, 09 Mar 2017 16:43:34 GMT

Ok will look into it.

Re: Set analysis to equal a calculated concatenated value

vishsaggi — Thu, 09 Mar 2017 19:30:09 GMT

There is one thing we have to use to get the correct cost. Still trying to find out. However, would like to take some suggestions from stalwar1‌.

Hello sunny, when you get time can you look into this attached qvw file.

Mainly the expression which has a set analysis. Need to display the cost of that specific combinedname.

Re: Set analysis to equal a calculated concatenated value

sunny_talwar — Thu, 09 Mar 2017 19:47:32 GMT

I am not sure what is needed here.... Is this needed in the Freight Table Export - Detail table or are we talking about creating a new table.

Also, I feel like we cannot use Set Analysis since we want the set modifier expression to be evaluated on every dimension. Set analysis cannot do this, so I feel the alternative might be to use Aggr().

Best,

Sunny

Re: Set analysis to equal a calculated concatenated value

Anonymous — Thu, 09 Mar 2017 19:54:16 GMT

I think you'll need a separate dimension either as aggr() or within the script... but I don't understand it too...

Re: Set analysis to equal a calculated concatenated value

vishsaggi — Thu, 09 Mar 2017 19:59:54 GMT

Yes, we have to use Aggr Function here. I will tell you the details. Select the values as i did and if you see for the expr palletCost, i have been trying to get the cost value like 126, 150, 150 for its respective Area and PalletSpaces. But it always giving 0.00. Let me know if i am not clear.

Re: Set analysis to equal a calculated concatenated value

sunny_talwar — Thu, 09 Mar 2017 20:09:18 GMT

May be this?

Sum(Aggr(If(PalletSpaceCost_Area&(ceil(sum(if(ad_zone =1, (idh_qty_inv/idh_conv), ((idh_qty_inv/idh_conv)/2))))) = PalletSpaceCost_CombinedValue, PalletSpaceCost_Cost), ih_ship, ih_ship_date, ad__chr02, PriceFile_Miles, PalletSpaceCost_Area, PalletSpaceCost_CombinedValue))

Re: Set analysis to equal a calculated concatenated value

vishsaggi — Thu, 09 Mar 2017 20:17:20 GMT

Cant believe my self i did tried this way, but missed few Aggr dimensions to be added. So bad. Thanks a ton. To my understanding this is what Dayna looking for. Will have to check.

Dayna, please use Sunny's expression and let us know if this is what you are looking for.

Re: Set analysis to equal a calculated concatenated value

Dayna — Fri, 10 Mar 2017 10:39:18 GMT

It works!! Thank you all so much, appreciated!!!

Vishwarath, thank you for your help especially in getting us there