topic Re: Adding Conditions to Inline Map Load in QlikView

Adding Conditions to Inline Map Load

Tue, 23 Aug 2016 19:30:03 GMT

Hi, first question so please bear with me.

I currently use a functioning inline Map load that I want to update with a condition based on a value of another field.

Here is the current Load Map Script:

MAPPING:

MAPPING LOAD * INLINE [

Old, New

'Issued, Awaiting Payment', 'Placed'

'Issued, Awaiting Requirements', 'Placed'

'Completed', 'Placed'

];

MAP [Policy Status] using MAPPING;

The new condition would be a script line using this logic for all three values above

If [Policy Status] = 'Issued, Awaiting Payment' and isNull ([Pc Release Date]), 'Issued' else 'Placed'

If [Policy Status] = 'Issued, Awaiting Requirements' and isNull ([Pc Release Date]), 'Issued' else 'Placed'

If [Policy Status] = 'Completed' and isNull ([Pc Release Date]), 'Issued' else 'Placed'

Any idea how this should be written up in the script? I've attached a sample excel file.

Re: Adding Conditions to Inline Map Load

settu_periasamy — Wed, 24 Aug 2016 02:04:42 GMT

Try this

MAPPING:
Mapping LOAD * INLINE [
Old, New 
'Issued, Awaiting Payment', 'Placed'
'Issued, Awaiting Requirements', 'Placed'
'Completed', 'Placed'

];
//MAP [Policy Status] using MAPPING;


T1:
LOAD [Policy No],[Pc Release Date],
  if([Policy Status]='Issued, Awaiting Payment' and Len([Pc Release Date])=0,'Issued',ApplyMap('MAPPING',[Policy Status])) as [Policy Status];
LOAD * INLINE [
     Policy No, Pc Release Date, Policy Status
    93141162, 12/1/2015 0:00, Completed
    46063096, , Completed
    93141167, 12/24/2015 0:00, Completed
    46050778, , "Issued, Awaiting Payment"
    46062965, 11/18/2015 0:00, "Issued, Awaiting Requirements"
    46050784, 12/22/2015 0:00, "Issued, Awaiting Requirements"
    93140067, 7/1/2016 0:00, "Issued, Awaiting Payment"
    46063416, , "Issued, Awaiting Requirements"
    93139877, , "Issued, Awaiting Payment"
    81036629, , Completed
];

Re: Adding Conditions to Inline Map Load

Wed, 24 Aug 2016 13:47:26 GMT

Thank you for the suggested solution. I may have not accurately described my problem in my opening post.

The excel file I attached is just a sample. My actual working file has 100K+ policy numbers so writing all those policy numbers in the load script would not be doable.

So currently, through my current load script pasted above, I am able to change the value of those three [Policy Status] to 'Placed' without writing out all the policy numbers.

So is there another script solution to convert this formula for 100k+ policy numbers with the Map Load function:

If( [Policy Status] = 'Issued, Awaiting Payments' OR 'Issued, Awaiting Requirements' OR 'Completed' AND isNull ([Pc Release Date]), 'In Issue' else 'Placed')

Re: Adding Conditions to Inline Map Load

settu_periasamy — Wed, 24 Aug 2016 19:13:41 GMT

Hi Roberto,

You dont need to put all the policy number in the script. I just used inline load for sample. You can load your excel or qvd source instead of inline.

Re: Adding Conditions to Inline Map Load

Wed, 24 Aug 2016 23:53:34 GMT

Okay, I got your suggestion to work for the one condition ('Issued, Awaiting Payment') but how do you write it to include the other two conditions ('Issued, Awaiting Requirements' AND 'Completed')? I tried using OR but the results are not what I expect.

Your continued assistance is greatly appreciated.

if([Policy Status]='Issued, Awaiting Payment' or 'Issued, Awaiting Requirements' or 'Completed' and Len([Pc Release Date])=0,'Issued',ApplyMap('MAPPING',[Policy Status])) as [Policy Status],

Re: Adding Conditions to Inline Map Load

Fri, 26 Aug 2016 14:46:58 GMT

Thanks to Settu's help, I got the results I wanted with a slight amendment to his formula so that all conditions are satisfied.

Here it is:

if(match([Policy Status],'Issued, Awaiting Payment','Issued, Awaiting Requirements', 'Completed') and Len([Pc Release Date])=0,'Post Issue',ApplyMap('MAPPING',[Policy Status])) as [Policy Status],

Re: Adding Conditions to Inline Map Load

settu_periasamy — Fri, 26 Aug 2016 14:52:29 GMT

Try like this..

If( ( [Policy Status]='Issued, Awaiting Payment' or
[Policy Status] = 'Issued, Awaiting Requirements' or
[Policy Status] = 'Completed') and Len([Pc Release Date])=0,'Issued','Placed') as [Policy Status]

Re: Adding Conditions to Inline Map Load

Fri, 26 Aug 2016 14:58:00 GMT

Thanks, I got it to work using Match but I am sure that will work as well. Your time in assisting me is much appreciated.