topic Re: Nasted IF statement for create filtering button with groups of orders in Connectivity & Data Prep

Nasted IF statement for create filtering button with groups of orders

SingSing16 — Thu, 03 Aug 2023 10:32:50 GMT

I have to create filtering button with groups of orders. It should be called 'Order Type Flag':

FIRST bucket L100','108','120','125','150' should be 'Project Order Types',

SECOND BUCKET: 102','105','103','155,'126,'107,'106','130','110','300' should be called 'Project No-Order Types'

THIRD BUCKET:,'SA','202','201','501','203','600','-','800','500','250','502' should be called 'Project Order Cther Types'

I tried with nasted IF statement as below but it do not works and show me in filter only one bucket.

Please support me.

if(match("Order_Type__c",'100','108','120','125','150'),
if(match("Order_Type__c",'102','105','103','155,'126,'107,'106','130','110','300'),
'Project Order Types',
if(match("Order_Type__c",'SA','202','201','501','203','600','-','800','500','250','502'),'Project No-Order Types','Project Order Cther Types'))) as 'Order Type Flag',

Re: Nasted IF statement for create filtering button with groups of orders

Or — Thu, 03 Aug 2023 10:55:21 GMT

That statement doesn't seem to make much sense as it's written. No row is ever going to match the first set of conditions because they're mutually exclusive.

You would want to use:

If(Match(FirstBucketHere),'Bucket1',

If(Match(SecondBucketHere),'Bucket2',

If(Match(ThirdBucketHere),'Bucket3','Others')))

Re: Nasted IF statement for create filtering button with groups of orders

BrunPierre — Thu, 03 Aug 2023 10:53:41 GMT

Try below.

if(
match("Order_Type__c", '100','108','120','125','150'),
'Project Order Types',
if(
match("Order_Type__c", '102','105','103','155','126','107','106','130','110','300'),
'Project No-Order Types',
if(
match("Order_Type__c", 'SA','202','201','501','203','600','-','800','500','250','502'),
'Project Other Types',
'Other'
)
)
) as [Order Type Flag]

Re: Nasted IF statement for create filtering button with groups of orders

Aurelien_Martinez — Thu, 03 Aug 2023 11:39:35 GMT

Hi,

The best way to do that is the mapping table and the applymap function.

https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/Mapping.htm

https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/Scripting/MappingFunctions/ApplyMap.htm

Example :

_map: Mapping Load * Inline [ Order_Type__c, TypeFlg 100, Project Order Types 108, Project Order Types 102, Project No-Order Types 502, Project Order Cther Types ]; Load *, ApplyMap('_map', Order_Type__c, 'Other) as [Order Type Flag] From file ;