Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Just signed up as the above error is driving me crazy. Fairly new to Qlik but I've been through what I can from searching the answer and nothings worked so far. Hoping its something simple!
I've loaded my 2 sets of data into a temp table (tmp) and now looking to bring that back with some extra calculations tagged on.
currently have the below:
data:
Load
BusType,
Division,
Region,
Area,
BusinessUnitName as [Business],
[Description] as [PY BusType],
if(BusType = 'Renewal', addyears([EffectiveDate],1), EffectiveDate) as [Effective Date],
PolicyRef,
OriginalDebt,
Commission,
PCL as PremiumCredit,
TerminationDate,
TermCode,
if(BusType = 'Renewal', if(TermCode <> 'canc-led',1, if(Termcode is NULL,1,0)), if Termcode IS NULL,1,0) as [Invited]
// if((termcode is null or (termcode = 'MTC')) and BusType = 'Renewal', 1, 0) as [Renewed],
// if(TermCode = 'Canc-led', 1, 0) as [Cancelled],
// if(TermCode = 'Lapsed', 1, 0) as [Lapsed]
resident tmp;
drop Table tmp;
The 4th if statement from the bottom if the one giving me issues. Originally this was something like
IF ((BusType = 'Renewal' and TermCode <> 'canc-led') or Termcode is NULL, 1, 0) as [Invited]
but it didn't seem to like the 'and' in the statement so following some reading I made it a nested if.
I don't think I've missed any ')' in there.. The other commented out if statements will need to be added too but I'm tackling one at a time so I know where my culprits are! (if there's anything wrong with those anyone can spot please let me know too)
Thanks everyone!
Try
if(BusType = 'Renewal', if(TermCode <> 'canc-led',1, if(IsNull(Termcode),1,0) ), if(Isnull(Termcode),1,0) )
as [Invited]
Try
if(BusType = 'Renewal', if(TermCode <> 'canc-led',1, if(IsNull(Termcode),1,0) ), if(Isnull(Termcode),1,0) )
as [Invited]
May be this might work:
If((BusType = 'Renewal' and (TermCode not Like 'canc-led')) or Len(Trim(Termcode)) = 0, 1, 0) as [Invited]
swuehl, you absolute legend!
I re wrote the next if statement using that logic too and the whole script is working now.
Thanks very much!