Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
paulm
Contributor III
Contributor III

Apply map replacing IF

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

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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.

View solution in original post

5 Replies
paulm
Contributor III
Contributor III
Author

Hi Guys,

Anyone able to help with this?

Thanks,


Paul

tanelry
Partner - Creator II
Partner - Creator II

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
Champion III
Champion III

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
Contributor III
Contributor III
Author

Hi John,

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

Paul

Not applicable

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