Using a new created field within an If statement - "Field Not Found" Error Message

[qlikwiz]

Hello, 

I'm loading an excel sheet into Qlik. However, I created new columns using the data load editor and I'm trying  to use these new columns in certain if statements. As I try to load the data, I get an error message saying that those fields are not found. The fields which are not found are [DM] and [YEAR]. Seems like an easy task but I'm new to Qlik. Would appreciate your help. Below is the script: 

LOAD

[PT_AGE],

[DOS],

[TYPE1],

[TYPE2],

[YR_DIFF],

Date([TSH_DATE] ) AS [TSH_DATE],

Date([MICROALBUMIN_DATE] ) AS [MICROALBUMIN_DATE],

Date([LIPID_DATE] ) AS [LIPID_DATE],

    Year([DOS]) - Year ([LIPID_DATE]) AS [YEAR],

    If(([TYPE1] = 1 and [TYPE2] = 0) or ([TYPE1] = 1 and [TYPE2] = 1), 1, 0) AS [DM],

    If([DM] = 1 and [PT_AGE] >= 11 and [YR_DIFF] > 5 and [MICROALBUMIN_DATE] <> 'NULL', 'Compliant', 'Overdue') AS [Microalb_Comp_Type1],

    If([TYPE2] = 1 and [MICROALBUMIN_DATE] <> 'NULL', 'Compliant', 'Overdue') AS [Microalb_Comp_Type2],

  If([DM] = 1 and [TSH_DATE] <> 'NULL','Compliant','Overdue') AS [TSH_Comp_Type1],

  If([DM] = 1 and [PT_AGE] >= 11 and [LIPID_DATE] <> 'NULL', 'Compliant', 'Overdue') AS [Lipid_Comp_Type1],

  If([TYPE2] = 1 and [LIPID_DATE] <> 'NULL' and [YEAR] <= 1 , 'Compliant', 'Overdue') AS [Lipid_Comp_Type2]

 FROM [lib://AttachedFiles/C31_Youssef_09212023_v2.xlsx]

(ooxml, embedded labels, table is Sheet1);

[MayilVahanan · Accepted solution]

HI

Try with preceeding load, because we can't use the new field column in the same load.

Load *,
  
    
    If([DM] = 1 and [PT_AGE] >= 11 and [YR_DIFF] > 5 and [MICROALBUMIN_DATE] <> 'NULL', 'Compliant', 'Overdue') AS [Microalb_Comp_Type1],
    If([TYPE2] = 1 and [MICROALBUMIN_DATE] <> 'NULL', 'Compliant', 'Overdue') AS [Microalb_Comp_Type2],
    
  If([DM] = 1 and [TSH_DATE] <> 'NULL','Compliant','Overdue') AS [TSH_Comp_Type1],
    
  If([DM] = 1 and [PT_AGE] >= 11 and [LIPID_DATE] <> 'NULL', 'Compliant', 'Overdue') AS [Lipid_Comp_Type1],
  If([TYPE2] = 1 and [LIPID_DATE] <> 'NULL' and [YEAR] <= 1 , 'Compliant', 'Overdue') AS [Lipid_Comp_Type2];

LOAD

[PT_AGE],

[DOS],

[TYPE1],

[TYPE2],

[YR_DIFF],

Date([TSH_DATE] ) AS [TSH_DATE],

Date([MICROALBUMIN_DATE] ) AS [MICROALBUMIN_DATE],

Date([LIPID_DATE] ) AS [LIPID_DATE],

    Year([DOS]) - Year ([LIPID_DATE]) AS [YEAR],

    If(([TYPE1] = 1 and [TYPE2] = 0) or ([TYPE1] = 1 and [TYPE2] = 1), 1, 0) AS [DM]

 FROM [lib://AttachedFiles/C31_Youssef_09212023_v2.xlsx]

(ooxml, embedded labels, table is Sheet1);

[qlikwiz]

Thank you very much!!!