Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a below Script which fails in Qlik Sense stating error messages as Invalid Expression please could anyone help?
Script is as below:
[Data]
LOAD
Sellg_div_cd,
Region,
District,
EMP,
Layer,
Prod_NM,
Current,
Baseline,
PRO_Baseline,
Wk_End
FROM [lib://Data/Data.xlsx]
(ooxml, embedded labels, table is [Non-Retail]);
Concatenate
LOAD
Region,
District,
EMP,
Layer,
Prod_NM,
Current,
Baseline,
PRO_Baseline,
Wk_End
FROM [lib://Data/Data.xlsx]
(ooxml, embedded labels, table is [Retail]);
[EMP]:
Load EMP,
If(Prod_NM ='ABC' OR Prod_NM='XYZ',Sum(PRO_Baseline))AS DRV_BASELINE,
If(Prod_NM='ABC' or Prod_NM='XYZ',Sum(Current))AS DRV_Current,
If(Prod_NM='PQR',Sum(PRO_Baseline))AS MV_BASELINE,
If(Prod_NM='PQR',Sum(Current))AS MV_Current
Resident Data
Order by EMP;
Error I receive while running this code is Invalid Expression.
I am have one excel with Multiple sheets, I need to load data from all sheets into one single table.
I have to take Product ABC and Product XYZ and Get there Sum for baseline values and Current values.
Once I Receive these values there are many more calculations to be done on these values hence need them to be calculated in Script instead Of Expression.
Please help.
Try this
[EMP]:
Load EMP,
If(Prod_NM ='ABC' OR Prod_NM='XYZ',Sum(PRO_Baseline))AS DRV_BASELINE,
If(Prod_NM='ABC' or Prod_NM='XYZ',Sum(Current))AS DRV_Current,
If(Prod_NM='PQR',Sum(PRO_Baseline))AS MV_BASELINE,
If(Prod_NM='PQR',Sum(Current))AS MV_Current
Resident Data
Group by Prod_NM
Order by EMP;
[EMP]:
Load EMP,
If(Prod_NM ='ABC' OR Prod_NM='XYZ',Sum(PRO_Baseline))AS DRV_BASELINE,
If(Prod_NM='ABC' or Prod_NM='XYZ',Sum(Current))AS DRV_Current,
If(Prod_NM='PQR',Sum(PRO_Baseline))AS MV_BASELINE,
If(Prod_NM='PQR',Sum(Current))AS MV_Current
Resident Data
GROUP BY EMP, Prod_NM
Order by EMP;
OR
[EMP]:
Load EMP,
Sum(If(Prod_NM ='ABC' OR Prod_NM='XYZ',PRO_Baseline))AS DRV_BASELINE,
Sum(If(Prod_NM='ABC' or Prod_NM='XYZ',Current))AS DRV_Current,
Sum(If(Prod_NM='PQR',PRO_Baseline))AS MV_BASELINE,
Sum(If(Prod_NM='PQR',Current))AS MV_Current
Resident Data
GROUP BY EMP
Order by EMP;