If...then...elseif...else...endif

[Anonymous]

Hi community.

Could someone please tell me why this script doesn't work properly. it runs but seems to ignore the IF statement and just runs the code after "ELSE". Think something is wrong with my if "condition'' as everything fall through to the "else" bucket. (even if it does meet the condition criteria). I'm thinking it could be a syntax issue?

Facts_Truck_Cost:

LOAD

  MonthYear,

  gl_code,

  Truck_ID,

  Margin_Flag,

  Allocation_Flag,

  Truck_Cost,

  SubField(gl_code,'-',4) as account_code

FROM [..\Testing\Qlik MODEL QVD\Direct_Cost_Trucks_Table.qvd] (qvd);

\

// Allocation of each gl account with special portions and all gl accounts to truck legs

//////////////////// 20400 (Interislander Cost to SI routes only)

IF (SubField(gl_code,'-',4)) = '20400' THEN

  Facts_Truck_Cost_Allocation:

  NoConcatenate

  Load *,

  if(Allocation_Flag = 'Distance','IntD',

  if(Allocation_Flag = 'Weight','IntW',

  if(Allocation_Flag = 'Activity','IntA',

  if(Allocation_Flag = 'Revenue','IntR',0))))

  as Truck_Cost_leg

  Resident Facts_Truck_Cost

;

//////////////////// 20300 (Subcontractor Cost to Subbies and subby customers only)

ELSEIF(SubField (gl_code,'-',4)) = '20300' THEN

  Facts_Truck_Cost_Allocation:

  NoConcatenate

  Load *,

  if(Allocation_Flag = 'Distance','SUBD',

  if(Allocation_Flag = 'Weight','SUBW',

  if(Allocation_Flag = 'Activity','SUBA',

  if(Allocation_Flag = 'Revenue','SUBR',0))))

  as Truck_Cost_leg

  Resident Facts_Truck_Cost

;

//////////////////// ALL OTHER (NON SPECIAL) MTD PORTION TO ALL LEGS

ELSE

  Facts_Truck_Cost_Allocation:

  NoConcatenate

  Load *,

  if(Allocation_Flag = 'Distance','OtherD',

  if(Allocation_Flag = 'Weight','OtherW',

  if(Allocation_Flag = 'Activity','OtherA',

  if(Allocation_Flag = 'Revenue','OtherR',0))))

  as Truck_Cost_leg

  Resident Facts_Truck_Cost

;

ENDIF

Drop table Facts_Truck_Cost;

Result:

EDIT: Result image added

[petter]

Try to do you if tests with the account_code directly as you have already prepared it in your first load. You might have a hard to detect problem with trailing spaces in your gl_code. If you have trailing space or spaces in this field your logic will not work. The spaces are of course invisible to the human eye depending on where they appear.

So write this instead:

IF account_code = 20400 THEN

....

ELSEIF account_code = 20300 THEN

....

It is a shorter code anyway and according to your screenshot the account codes when being separated out in the first LOAD it will convert them into numbers and will remove automatically the trailing spaces.

[rahulpawarb · Accepted solution]

Hello Chantelle,

Trust that you are doing well!

Considering your logic, SubField Function will not work as expected. Please use below given sample script:

Facts_Truck_Cost:
LOAD MonthYear,
     gl_code,
     Truck_ID,
     Margin_Flag,
     Allocation_Flag,
     Truck_Cost,
     If(SubField(gl_code,'-',4) = '20400', 'Int', If(SubField(gl_code,'-',4) = '20300', 'SUB', 'Other'))
     & If(Allocation_Flag = 'Distance','D',
          If(Allocation_Flag = 'Weight','W',
             If(Allocation_Flag = 'Activity','A',
                If(Allocation_Flag = 'Revenue','R',0)))) AS Truck_Cost_leg
     /*
     If(SubField(gl_code,'-',4) = '20400', 'Int', If(SubField(gl_code,'-',4) = '20300', 'SUB', 'Other'))
     & Left(Allocation_Flag, 1) AS Truck_Cost_leg
     */
FROM
Direct_Cost_Trucks_Table.xlsx
(ooxml, embedded labels, table is Facts_Truck_Cost);

Hope this will be helpful.

Regards!

Rahul

[shivkumar300]

request you to send me a sample data.

[petter]

With all due respects Sir - the SubField()-function can be used BOTH inside a LOAD and outside of a LOAD in a load-script. There is no doubt about it - if you don't believe me do a real test yourself.

In a load statement it is either an aggregation function - if it has two parameters, or a regular string extraction function if it has three parameters. Outside a load-statement it is just a string extraction function that need three parameters - just as it would be in the UI expressions in a running application.

[rahulpawarb]

Hello Petter,

Sincere apologies for not being clear in prior response . I 100% agree with you; I mean to say SubField function will not work as expected considering logic written by Chantelle.

Once again extremely sorry for putting ambiguous response.

Regards!

Rahul

[Anonymous]

Thank you for your response Petter. I tried your suggestion but unfortunately it still behaves the same. So I must have something else wrong also.  I have however managed to get the desired result with nested if functions. It looks a bit clunky but works 😉