alternative to IF statement

[jkflash1964]

I am fairly new to Qlik Sense, having dealt primarily with Crystal Reports.

In Crystal, I would create a formula for Customer that would have perhaps upwards of 50 IF-Then statements.  Then based on that formula, Sales would be calculated for the year.  Attached is the formula (I hope).

For QS, I want to be able to write something that would replace the IF statement since it is rather lengthy and I've seen some comments about how QS doesn't seem to deal well with large formulas like this.

Not sure what else to provide, but any help will be appreciated.

[ArnadoSandoval]

Hi @jkflash1964 

You posted the very long IF statement, which is resolving a mapping for the "customer.cust-division" field, with some additional rules, but basically the IF statement is mapping cust-division to a more friendly description (or business description).

I do not know if your back-end database, the one Crystal Reports is sourcing its data from has a table with the friendly descriptions, I will assume it does not have such a table.

With Qlik Sense perhaps the best approach to handle this scenario, based on the information you shared are:

• Mapping  Load
• ApplyMap 

Mapping-Load and ApplyMap is kind of the familiar look-up structure in Excel; I did some cleansing to the original IF condition you attached, its step 01 removed all the else parts of the if statements, I keep some AND but I consider these IFs as Special Rules, and any comment line found within it, the second file, step 02 removed all the IF and THEN from the if statements, split the [ val1, val2, val3, ... ] into different lines, basically creating a key-value list from your if statement, I excluded those lines with AND as I consider them Special Rules, to be implemented in the next step of the load-script (I am not including that in this reply, as I want you to get the logic of the mapping load first.)

Note: The attached DOC files are actually TXT files, this interface does not allow us to attach TXT files

Note 2: I am cutting my reply short here, as in my next reply, I include the script performing the Mapping-Load and ApplyMap.

[ArnadoSandoval]

Hi @jkflash1964 

The Qlik Community interface refused the attachments of TXT or DOC files, so, I will add their content as text here in this reply:

The Cleansing-step01.txt file:

if {custname.cust-division} = ["AmazonSC", "AmazonVC"] and {product.code-element-3} = ["02", "16", "19"] then "Amazon24G"
if {custname.cust-division} = ["AmazonSC", "AmazonVC"] then "AmazonBe3"
if {custname.cust-division} = "AM Retail" then "AMretail"
if {custname.cust-division} = "Ashley" then "AshleyStewart"
// if {customer_ar.billing-customer-key} = "00068418" then "Backstage"
if {custname.cust-division} = "Backstage" then "Backstage"
// if {customer_ar.billing-customer-key} = "00089183" then "BargainHunt"
if {custname.cust-division} = "Coat" and {@Gender} = "Mens" then "BCFmens"
if {custname.cust-division} = "Coat" then "BCF"
if {custname.cust-division} = "Bealls" and {product.code-element-3} = ["02", "04", "16", "19"] then "BeallsShoes"
if {custname.cust-division} = "Bealls" and {custname.group-id} = "OffPrice" then "BeallsOutlet"
if {custname.cust-division} = "Bealls" then "Bealls"
if {custname.cust-division} = "Big Lots" then "BigLots"
if {custname.cust-division} = "BJs" then "BJs"
if {custname.cust-division} = "BootBarn" then "BootBarn"
if {custname.cust-division} = "Boscovs" then "Boscovs"
if {custname.cust-division} = "BBB" then "BuyBuyBaby"
// if {customer_ar.billing-customer-key} = "00086600" then "CanadianTire"
if {custname.cust-division} = "Catherines" then "Catherines"
if {custname.cust-division} = ["Citi Trends", "Discovery", "Eloquii", "Fabco", "WorknGear"]  then "ChantalMisc"
// if {customer_ar.billing-customer-key} = ["00076277", "00076283", "00088999", "00076315"] then "ChantalMisc"
if {custname.cust-division} = "ChildPlace" then "ChildrensPlace"
if {custname.cust-division} = "Christmas" then "Christmas"
if {custname.cust-division} = "Coppell" then "Coppel"
if {custname.cust-division} = "CostcoMexico" then "CostcoMexico"
if {custname.cust-division} = "Costco UK" then "CostcoUK"
// if {customer_ar.billing-customer-key} = "00089645" then "PriceSmart"
// if {customer_ar.billing-customer-key} = "00022385" then "Costco"
if {custname.cust-division} = "Costco" then "Costco"
if {custname.cust-division} = "DDs" then "DDs"
if {custname.cust-division} = "Dillards" then "Dillards"
if {custname.cust-division} = "DollarGen" then "DollarGeneral"
if {custname.cust-division} = "DollarTree" then "DollarTree"
if {custname.cust-division} = "DSW" then "DSW"
if {custname.cust-division} = "Dunhams" then "Dunhams"
if {custname.cust-division} = "Destination" then "DXL"
if {custname.cust-division} = ["Belk", "Meijer", "NewYork&Co", "Saks", "The Buckle", "Torrid"]  then "EllenMisc"
// if {customer_ar.billing-customer-key} = "00069402" then "EllenMisc"
if {custname.cust-division} = "FamilyDollar" then "FamilyDollar"
if {custname.cust-division} = "Fred" then "FredMeyer"
if {custname.cust-division} = "Gabriel" then "GabrielBrothers"
// if {customer_ar.billing-customer-key} = "00057944" then "GCC"
if {custname.cust-division} = "GORDONMILLS" then "GordonMills"
if {custname.cust-division} = "Hamricks" then "Hamricks"
if {custname.cust-division} = "JCP" then "JCP"
if {custname.cust-division} = "Journeys" then "Journeys"
// if {custname.cust-division} = "Kohls" and {@Gender} = "Mens" then "KohlsMens"
if {custname.cust-division} = "Kohls" then "Kohls"
if {custname.cust-division} = "Lane Bryant" then "LaneBryant"
if {custname.cust-division} = "LifeIsGood" then "LIG"
// if {customer_ar.billing-customer-key} = "00044705" then "MacysDTC"
// if {customer_ar.billing-customer-key} = "00000421" then "Marshalls"
if {custname.cust-division} = "Menards" then "Menards"
if {custname.cust-division} = ["Big5", "HEB", "LIDL"]  then "NathanMisc"
// if {customer_ar.billing-customer-key} = "00053192" and {product.code-element-3} = ["02", "04", "16", "19"] then "NavyExchangeKids"
// if {customer_ar.billing-customer-key} = "00053192" then "NavyExchange"
// if {customer_ar.billing-customer-key} = "00079616" then "Newton"
if {custname.cust-division} = "NordRack" then "NordRack"
if {custname.cust-division} = "Nordstrom" and {product.code-element-3} = ["02", "04", "16", "19"] then "NordstromKids"
if {custname.cust-division} = "Nordstrom" then "Nordstrom"
if {custname.cust-division} = "Ocean" then "Ocean"
if {custname.cust-division} = "Ollies" then "Ollies"
if {custname.cust-division} = "Pendleton" then "PendletonRetail"
if {custname.cust-division} = "REI" then "REI"
if {custname.cust-division} = "Steinmart" and {@Gender} = ["Ladies", "Acc"] then "RichelleMisc"
if {custname.cust-division} = "Steinmart" then "ArthurMisc"
if {custname.cust-division} = ["Cato", "Century 21", "NorthernTool", "OffBroadway", "Therapy"] then "RichelleMisc"
// if {customer_ar.billing-customer-key} = ["00047905", "00052665"] then "RichelleMisc"
if {custname.cust-division} = "MCX" and {product.code-element-3} = ["02", "04", "16", "19"] then "ToddMisc"
if {custname.cust-division} = "MCX" then "RichelleMisc"
// if {customer_ar.billing-customer-key} = "00053115" then "RobeezCA"
// if {customer_ar.billing-customer-key} = "00053111" then "RobeezUS"
if {custname.cust-division} = "Ross" then "Ross"
if {custname.cust-division} = "Sams" then "Sams"
if {custname.cust-division} = "Carnival" then "ShoeCarnival"
if {custname.cust-division} = "Sportsman" then "Sportsmans"
if {custname.cust-division} = "Talbots" then "Talbots"
if {custname.cust-division} = "Target" then "Target"
// if {customer_ar.billing-customer-key} = "00080743" then "TJXAustralia"
// if {customer_ar.billing-customer-key} = "00035520" then "TKMaxx"
// if {customer_ar.billing-customer-key} = "00000682" then "Winners"
if {custname.cust-division} = "TJX" and {@Gender} = "Mens" then "TJMaxxMens"
if {custname.cust-division} = "TJX" then "TJMaxx"
// if {customer_ar.billing-customer-key} = ["00045242", "00087603"] then "ToddMisc"
// if {customer_ar.billing-customer-key} = "00058564" then "Trumpette"
if {custname.cust-division} = "Tuesday" then "TuesMorn"
if {custname.cust-division} = "Variety" then "Variety"
if {custname.cust-division} = "VF Outlet" then "VFO"
if {custname.cust-division} = "Von Maur" and {product.code-element-3} = ["02", "16", "19"] then "VonMaurShoes"
if {custname.cust-division} = "Von Maur" then "VonMaurSocks"
if {custname.cust-division} = "Walmart" then "Walmart"
// if {customer_ar.billing-customer-key} = ["00090967", "00087777"] then "YoelaMisc"
if {custname.cust-division} = "Zappos" then "Zappos"
// if {customer_ar.billing-customer-key} = "00019949" then "Zulily"
// if {custname.group-id} = "Canada" and {product.code-element-3} <> "11" then "SpecialtyCA"
// if {product.code-element-3} = "11" then "SpecialtyPendleton"
SpecialtyUS

The Cleansing-step02.txt file:

AmazonSC      ,  AmazonBe3
AmazonVC      ,  AmazonBe3
AM Retail     ,  AMretail
Ashley        ,  AshleyStewart
Backstage     ,  Backstage
Coat          ,  BCF
Bealls        ,  Bealls
Big Lots      ,  BigLots
BJs           ,  BJs
BootBarn      ,  BootBarn
Boscovs       ,  Boscovs
BBB           ,  BuyBuyBaby
Catherines    ,  Catherines
Citi Trends   ,  ChantalMisc
Discovery     ,  ChantalMisc
Eloquii       ,  ChantalMisc
Fabco         ,  ChantalMisc
WorknGear     ,  ChantalMisc
ChildPlace    ,  ChildrensPlace
Christmas     ,  Christmas
Coppell       ,  Coppel
CostcoMexico  ,  CostcoMexico
Costco UK     ,  CostcoUK
Costco        ,  Costco
DDs           ,  DDs
Dillards      ,  Dillards
DollarGen     ,  DollarGeneral
DollarTree    ,  DollarTree
DSW           ,  DSW
Dunhams       ,  Dunhams
Destination   ,  DXL
Belk          ,  EllenMisc
Meijer        ,  EllenMisc
NewYork&Co    ,  EllenMisc
Saks          ,  EllenMisc
The Buckle    ,  EllenMisc
Torrid        ,  EllenMisc
FamilyDollar  ,  FamilyDollar
Fred          ,  FredMeyer
Gabriel       ,  GabrielBrothers
GORDONMILLS   ,  GordonMills
Hamricks      ,  Hamricks
JCP           ,  JCP
Journeys      ,  Journeys
Kohls         ,  Kohls
Lane Bryant   ,  LaneBryant
LifeIsGood    ,  LIG
Menards       ,  Menards
Big5          ,  NathanMisc
HEB           ,  NathanMisc
LIDL          ,  NathanMisc
NordRack      ,  NordRack
Nordstrom     ,  Nordstrom
Ocean         ,  Ocean
Ollies        ,  Ollies
Pendleton     ,  PendletonRetail
REI           ,  REI
Steinmart     ,  ArthurMisc
Cato          ,  RichelleMisc
Century 21    ,  RichelleMisc
NorthernTool  ,  RichelleMisc
OffBroadway   ,  RichelleMisc
Therapy       ,  RichelleMisc
MCX           ,  RichelleMisc
Ross          ,  Ross
Sams          ,  Sams
Carnival      ,  ShoeCarnival
Sportsman     ,  Sportsmans
Talbots       ,  Talbots
Target        ,  Target
TJX           ,  TJMaxx
Tuesday       ,  TuesMorn
Variety       ,  Variety
VF Outlet     ,  VFO
Von Maur      ,  VonMaurSocks
Walmart       ,  Walmart
Zappos        ,  Zappos
SpecialtyUS

Ok, my next reply focus on the load script, implementing this customer-division key-value data.

[ArnadoSandoval]

Hi @jkflash1964 

Attached in a QVF file implementing the first stage of the ApplyMap; below its load script; first it loads the Key-Value combination into the table Mapping_Division_Friendly using a Mapping Load,  then it loads dummy customer records from the same XL file!

Once you understand this step we will focus on the Special Rules mentioned above.

Mapping_Division_Friendly:
Mapping LOAD
    "custname.cust-division",
    friendly_name
FROM [lib://Sample_Data/Cust-Division_Mapping.xlsx]
(ooxml, embedded labels, table is Cust_Division_KEY_VALUE);

NoConcatenate
Customers:
LOAD
    custcode,
    "custname.cust-division",
    ApplyMap('Mapping_Division_Friendly', "custname.cust-division", 'SpecialtyUS' ) As Friendly_Description
FROM [lib://Sample_Data/Cust-Division_Mapping.xlsx]
(ooxml, embedded labels, table is Customers);

 Hope this helps,