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
| Priority | Fruit | Cause | Color | Loc | ShipDate | PickDate | Harvester | Grower | GrowerLocation | FRCause | GrowerType | FRImper | FM | Status |
| 1 | Apple | AJ | Green | BA or BB | | <4/1/2015 | | | | | | | 36390 | Imp |
| 2 | | AH | Red | DA or DE or GA | | <10/24/2017 | | | | | | | 39757 | Imp |
| 3 | | AE | | GA or AA | | | Shake or Drop | | | | | | 47915 | Imp |
| 4 | Banana | AP or AA | | HB | <1/6/2016 | | Picker | BobOrchard | | | | | 35300 | Imp |
| 5 | Apple | AP or AA | | HB | <9/9/2015 | | Picker | | Texas | | | | 37781 | Imp |
| 6 | | AG | | HB | | <4/10/2013 | Picker | | | AG | | | 33429 | Imp |
| 7 | | | | | | <3/18/2016 | | | | AB | | | 46227 | Imp |
| 8 | | | | | | | | | | AF | GMO | FL | 42168 | Imp |
| 9 | | | | | | | | | | AE | Organic | AI | 30735 | Pen |
| 10 | | | | | | <1/31/2015 | | | | AD | | | 38963 | Imp |
| 11 | | | | | | <7/10/2015 | | | | BH | | | 38697 | Pen |
| 12 | | DD | | | | | | Hamilton | | | | | 40099 | Pen |
| 13 | | AS or ED | | | | <9/25/2015 | | | | | | | 43294 | Imp |
| 14 | | | | | | <12/16/2016 | | | | | | | 31869 | Imp |
| 15 | Pear | AK | | | <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,
