topic Assign a value to more than one category in App Development

Assign a value to more than one category

RyugaHideki — Fri, 15 Nov 2024 20:51:11 GMT

Hello i'm trying to group sizes and the logic is something like this
Case
WHEN SIZE LIKE '251 - 499ML' AND PACK_SIZE IN ('300ml', '320ml', '330ml', '3960ml') AND Char_3='PLASTIC BOTTLE' THEN '300ML - 330ML'

WHEN SIZE LIKE '251 - 499ML' AND PACK_SIZE IN ('300ml', '320ml', '330ml', '3960ml','400ml', '450ml') AND Char_3='PLASTIC BOTTLE' THEN '250ML - 500ML'

else SIZE END AS SIZE,

in short i want the '300ML - 330ML' inside '250ML - 500ML' as well but doing it like this gets it excluded how can i address the problem

@MatheusC

Re: Assign a value to more than one category

AnsweringTuring — Tue, 15 Oct 2024 10:47:30 GMT

The issue you're facing is due to the order of the CASE statements. The first WHEN condition that evaluates to true will be the result, and subsequent conditions will be ignored.

To include '300ML - 330ML' within '250ML - 500ML', you need to reorder the CASE statements, putting the more specific condition ('300ML - 330ML') after the broader condition ('250ML - 500ML'). This way, if a value matches the '300ML - 330ML' condition, it will be assigned that label. Otherwise, it will be evaluated against the '250ML - 500ML' condition.

Here's the updated logic:

CASE
WHEN SIZE LIKE '251 - 499ML' AND PACK_SIZE IN ('300ml', '320ml', '330ml', '3960ml', '400ml', '450ml') AND Char_3='PLASTIC BOTTLE' THEN '250ML - 500ML'
WHEN SIZE LIKE '251 - 499ML' AND PACK_SIZE IN ('300ml', '320ml', '330ml', '3960ml') AND Char_3='PLASTIC BOTTLE' THEN '300ML - 330ML'
ELSE SIZE
END AS SIZE

Re: Assign a value to more than one category

SunilChauhan — Tue, 15 Oct 2024 11:11:51 GMT

As long as i know overlapping of groups/Bucket not possible. if you write in code in Qlik should not appear in Calculated field. there should be additional condition.

Re: Assign a value to more than one category

RyugaHideki — Tue, 15 Oct 2024 13:22:36 GMT

the problem with that is that it will exclude 300-330 from 250-500

Re: Assign a value to more than one category

RyugaHideki — Tue, 15 Oct 2024 13:23:12 GMT

I think so too, i was thinking of union or something but i do not know how to implement it

Re: Assign a value to more than one category

BuildItStrong — Tue, 15 Oct 2024 14:06:21 GMT

It looks like you have access to the data load editor. If this were me I might create two different buckets using two different case statements. One case statement would address once bucketing condition, and another case statement would address another bucketing condition. You could then roll up the various buckets to your needed reporting requirements in different ways: Maybe something like this using a nested select statement:

Select <fields with the bucket1 and bucket2, specialized groups derived from bucket1, bucket 2, and different supporting metadata, such as product, etc.>

from (

Select <various fields including product and other metadata>,

case statement one that created one bucket of data as bucket1,

case statement two that created a separate bucket of data as bucket2

from ...

) NestedSelectAlias

This would allow a single product to appear in more than one bucket at once, and you could use each bucket to meet the needs of your various different groupings in analytic displays. You are not necessarily limited to just 2 buckets. How may ways do you need to chop up this data? And you can return to add more buckets later without impacting existing analytics. Also note that you get better performance when you create buckets in load scripts than in the analytics itself.

I hope this helps.

Re: Assign a value to more than one category

RyugaHideki — Wed, 16 Oct 2024 07:05:09 GMT

if i get you right you want to do something similar to this

select

A.year,

A.PRODUCT,

case when A.size like etc.. as A.SIZE from

(select YEAR,

PRODUCT,

case when size etc as size) A

like this ?

Re: Assign a value to more than one category

BuildItStrong — Wed, 16 Oct 2024 12:57:00 GMT

In your example, there is only one case statement in the subselect. If you want a single record to appear in more than one bucket (presumably because you want to analyze it in more than one way), then you need more than one bucket. Focusing on the multiple buckets, it might look closer to:

Select

A.year,

A.PRODUCT,

A derived field that uses functions and equations to manipulate the values of SizeBucket1, SizeBucket2 as necessary

from

// Start of Subselect...

(select YEAR,

PRODUCT,

case when size etc as SizeBucket1

case when size etc as Sizebucket2) A

Use the Subselect to develop your various buckets in the lowest grain that you will need in your analysis. Pass the data from the Subselect into the upper select to perform any complex calculations needed from those lowest grain buckets. By performing the case statements in the subselect, you can manipulate the results of those case statements as though they were fields in the upper select. Using this technique you can perform complex processing of your multi-bucket data. Most SQL data engines will support case statements and subselects. Depending upon your specific needs, you may be able to achieve your goals with a simple collection of different buckets, and you may not need the select - subselect approach.

Regards,

Re: Assign a value to more than one category

RyugaHideki — Mon, 21 Oct 2024 07:48:33 GMT

i understand that appraoch and it's something i've thought of doing actually but it'll be an inconvience to have two size fields even if i concatenate the fields, thank you

Re: Assign a value to more than one category

BuildItStrong — Mon, 21 Oct 2024 12:28:49 GMT

I am very interested in hearing about your final solution.

Re: Assign a value to more than one category

RyugaHideki — Mon, 21 Oct 2024 13:03:53 GMT

i just decided to put a second reading in another sheet one has the sizes needed the first time and the second has the sizes that need to be combined in another way

there probably is different approaches to this for instance i could just alter the data from the database and create the category i want in the end similar approaches to this since we cannot double the elements cause the margin of error is really thin