Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm new to Qliksense and have two questions regarding the script below.
1) Getting the error, "Field 'Unit Sales' not found" for the query below and I'm not sure why
2) I'm using the alt("Avg Sell Price",0) and it isn't returning 0 for the null values. I'm pulling this information from an excel sheet and there are blanks in the "Avg Sell Price" that im trying to show as 0
Thank you for helping out!
CostcoAldi:
LOAD
"Scan Customer",
"Customer ID",
"Customer Product Description",
"Customer Code",
"Sales Week",
"Unit Sales",
"Avg Sell Price",
alt("Avg Sell Price",0)
FROM [lib://Demand Planning Customer Files (manassen_qvadmin)/Aldi-Costco/Aldi-Costco Scan.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);
Concatenate(CostcoAldi)
LOAD
MPITNO as "Customer Code",
MPPOPN as "Customer Ref",
MPREMK as Banner
FROM [lib://Raw Data (manassen_qvadmin)/MITPOP.qvd]
(qvd);
Concatenate(CostcoAldi)
LOAD
MUITNO as "Customer Code",
MUAUTP,
MUALUN,
MUDCCD,
MUCOFA,
MUCOFA * "Unit Sales" = Cases,
MUDMCF,
MUPCOF,
MUAUS1,
MUAUS2,
MUAUS3,
MUAUS4,
MUAUS5,
MUAUS6,
MUAUS9,
MUUNMU,
MUFMID,
MURESI,
MUTXID,
MURGDT,
MURGTM,
MULMDT,
MUCHNO,
MUCHID,
MULMTS,
MUPACT,
MUAUSC,
MUAUSB
FROM [lib://Raw Data (manassen_qvadmin)/MITAUN.qvd]
(qvd);
case1 : seems something wrong here MUCOFA * "Unit Sales" = Cases,
case2 : if(len(trim("Avg Sell Price"))=0,0,"Avg Sell Price")
Hello, it seems that the "Unit Sales" is not a field in the MITAUN qvd.
Keep in mind that the alt function is effective only when the field or expression is genuinely null or empty. In several instances, empty cells in Excel spreadsheets are not recognized as null or empty.
You might want to consider using the if function along with IsNull() or Len() functions to address this issue:
If(IsNull([Avg Sell Price]), 0, [Avg Sell Price])
or
If(Len([Avg Sell Price]) = 0, 0, [Avg Sell Price])