Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
toyokogyo12aturbo
New Contributor

Dynamic Nest If

Hello,
      I have dataset that I am trying to add new fields (FM and Status) based on layers of nested ifs which is currently 15 layers deep now using 12 fields. I need the ability to easily add more layers with any combination of the fields. In the table below, blank fields indicate that any value in the dataset is acceptable so long as the other fields are met.

I am currently using 12 different fields in nested ifs, but my dataset has 60+ fields and any of them could potentially be used on future additional ifs.

I don't want to edit the script every time I need to add a new layer. I envision adding another row of conditional values to the table below. The script would essentially review the conditions in the table (and priority) to apply the correct FM and Status value to the master dataset.

I've used Mapping Load and Apply Map in the past, but similarly it is not dynamic, and I’ve had to make a new map for every variant of conditional fields required. For example, rows 8 and 9 could use the same Mapping Load key as shown in Psuedocode below the table. That would still require nested ifs, just not as many (now).

I’ve been using QlikView for about 2 year, but it’s not my primary function and I certainly wouldn’t claim that my approach is the most efficient. Layers and layers of nested ifs does not seem efficient. If you have other recommendations. I’m certainly open to any ideas.

Thanks for your help. Shaun

 PriorityFruitCauseColorLocShipDatePickDateHarvesterGrowerGrowerLocationFRCauseGrowerTypeFRImperFMStatus
1AppleAJGreenBA or BB <4/1/2015      36390Imp
2 AHRedDA or DE or GA <10/24/2017      39757Imp
3 AE GA or AA  Shake or Drop     47915Imp
4BananaAP or AA HB<1/6/2016 PickerBobOrchard    35300Imp
5AppleAP or AA HB<9/9/2015 Picker Texas   37781Imp
6 AG HB <4/10/2013Picker  AG  33429Imp
7     <3/18/2016   AB  46227Imp
8         AFGMOFL42168Imp
9         AEOrganicAI30735Pen
10     <1/31/2015   AD  38963Imp
11     <7/10/2015   BH  38697Pen
12 DD     Hamilton    40099Pen
13 AS or ED   <9/25/2015      43294Imp
14     <12/16/2016      31869Imp
15PearAK  <6/30/2017       40792

Imp

 

 

FM_STATUSTEMP:

Load Table above

 

//Excerpt, additional Mapping Loads required for each variant of conditional field combinations

MAP_STATUS_B1:
MAPPING LOAD
     trim(FRCause) & '|' & trim(GrowerType) & '|' & trim(FRImper)   AS MAP_KEY,
     FMstatus   AS MAP_DESC
     Resident FM_STATUSTEMP where len(trim(Fruit))=0 and len(trim(Cause))=0 and len(trim(Color))=0 and len(trim(Loc))=0 and len(trim(ShipDate))=0 and len(trim(PickDate))=0 and len(trim(Harvester))=0 and len(trim(Grower))=0 and len(trim(GrowerLocation))=0 and len(trim(FRCause))>0 and len(trim(GrowerType))>0 and len(trim(FRImper))>0;

FM_STATUS:
Load
     if(FMstatus_D<>'Rem',FMstatus_D,
           if(FMstatus_C<>'Rem',FMstatus_C,
                if(FMstatus_B2<>'Rem', FMstatus_B2,
                     if(FMstatus_B1<>'Rem', FMstatus_B1,
                          if(FMstatus_A<>'Rem',FMstatus_A,'Rem'))))) as FMstatus;


//Excerpt, additional check fields and ApplyMap required for each variant of conditional field combinations

trim(FRCause) & '|' & trim(GrowerType) & '|' & trim(FRImper)   AS check_B1,
ApplyMap('MAP_STATUS_B1', trim(FRCause) & '|' & trim(GrowerType) & '|' & trim(FRImper), 'Rem') AS FMstatus_B1,

1 Reply
chrismarlow
Contributor III

Re: Dynamic Nest If

Hi,

See if anything in this link helps;

Building-dynamic-WHERE-clause

Post back any thoughts if you get stuck.

Cheers,

Chris.