Apply map replacing IF

[paulm]

Hi,

I am in the process of changing to QlikView 10 sr2.  Atpresent we have a massive IF statement of over 100 nested IFs.  As you mayknow there has been a cap of 100 placed on the number of nested IFs you canhave.  Due to this we have been attempting create a new way to process thedata.  The most obvious was creating a spreadsheet of all the attributesthat were in the IF and load them in through a mapping table.  It is atthis point I am struggling to find a solution.

Our current If does a wildmatch, (As shown below) which I cannot incorporate into an apply map.

if(wildmatch(BILL.DESCR_ON_BILL,

                       '*GP Night Energy*',

                       '*GP Night Storage*',

                       '*GPNS Night Energy*',

                       '*CB Night Energy*',

                       '*GPC Night Energy*',                                                      

                       '*Night units*',

                       '*Night Energy*',

                       '*GPNH Night Storage*',

                       '*Night KWH*',

                       '*Night¬ *',

                       '*Night Units*'

                       )>=1,

                       'Night Rate',

The value of BILL.DESCR_ON_BILL can have random values butwill contain one of the above somewhere within it.

Is there a simple way to replace this?  Maybe the applymap would not be the bestfunction for this?

Thanks in advance for your help.

Paul

[paulm]

Hi Guys,

Anyone able to help with this?

Thanks,

Paul

[tanelry]

Yeah, wildmap function would be nice.

The only alternative I see is to break down the table load into several sequences, one for each case.

Load time will probably increase.

Bills:

load

BILL.ID,

'Night Rate' as RateType

where

wildmatch(BILL.DESCR_ON_BILL,

'*GP Night Energy*','*GP Night Storage*','*GPNS Night Energy*','*CB Night Energy*','*GPC Night Energy*',

'*Night units*','*Night Energy*','*GPNH Night Storage*','*Night KWH*','*Night¬ *','*Night Units*')

;

SQL select ...

;

load

BILL.ID,

'Day Rate' as RateType

where

not exists(BILL.ID) and // avoid duplicates

wildmatch(BILL.DESCR_ON_BILL,

'*GP Day Energy*','*Day Units*')

;

SQL select ...

;

// finally:

load

BILL.ID,

'unknown' as RateType

where

not exists(BILL.ID) // all that did not qualify

;

SQL select ...

To manage it more nicely, I would probably put this into a for-loop using a separate "rules" table containing all RateTypes and match strings.

[johnw · Accepted solution]

The attached shows a way to "applymap" using wildcards from Rob Wunderlich's QlikView Cookbook, available here:

http://robwunderlich.com/downloads/

He theorizes that there's some upper limit to the size of the "mapping table", so it may not work any better for you than the nested ifs, but it may be worth a try.

[paulm]

Hi John,

Sorry I was away on leave.  This looks to work perfectly.  Thanks for you help!

Paul

[#6]

Hi Paul,

I used your suggestion and the code has been working successfully for a few months now. We recently updated to SR4 and migrated to a new server. Since then, the code has stopped working, it give an error that ')' is missing. Exactly the same script still works on the old setup.

Any advice or guidance would be much appreciated.

Thanks,

Waseem