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
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
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.
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.
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.
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
