Preceding Load & Group By

[danelooman]

Hello,

I am new to Qlik sense and have been trying to rack my brain about this. I have a table with products which are children of styles. in the table I have their sale amounts by transaction and the type of transaction. I am basically attempting to get a sum of sales for a particular type of sale for each style. I decided to do this by first mapping the styles then doing a load and then a preceding load & group by for the sum. When I comment out the preceding load and test load 1000 lines it works great and is done in a flash. The second I uncomment the group by it just sits and spins even with a limited load of 1000 lines. Do I have a syntax error or is this just a limitation of Qlik sense?

tmp:
LOAD STYLE_ID,
sum("Net Sales Rtl") as "Net Sales Rtl"
group by STYLE_ID;

LOAD
applymap('StyleMap' ,PRODUCT_ID) as STYLE_ID,
applymap('LocMap', O_LOCATION_ID) as CHANNEL,
"Net Sales Rtl"
FROM [somefile.qvd](qvd)
where DATE(DATE#(DATE_ID, 'YYYYMMDD')) >= Today(0)-7 and applymap('LocMap', O_LOCATION_ID) = 'B&M';

[jonathandienst]

Try without the preceding load:

LOAD applymap('StyleMap' ,PRODUCT_ID) as STYLE_ID,
sum("Net Sales Rtl") as "Net Sales Rtl"
FROM [somefile.qvd](qvd)
Where DATE(DATE#(DATE_ID, 'YYYYMMDD')) >= Today(0)-7 and applymap('LocMap', O_LOCATION_ID) = 'B&M'
Group By applymap('StyleMap' ,PRODUCT_ID);

[Vegar · Accepted solution]

You could try @jonathandienst fix, it will chould solve your issue. An alternative  approach is  to skip the preceding load in favour of a RESIDENT LOAD. Group by and peek in preceding load can seem unpredictable some times.

tmp:

LOAD
applymap('StyleMap' ,PRODUCT_ID) as STYLE_ID,
applymap('LocMap', O_LOCATION_ID) as CHANNEL,
"Net Sales Rtl"
FROM [somefile.qvd](qvd)
where DATE(DATE#(DATE_ID, 'YYYYMMDD')) >= Today(0)-7 and applymap('LocMap', O_LOCATION_ID) = 'B&M';

StyleNetSales:

LOAD

STYLE_ID,
sum("Net Sales Rtl") as "Net Sales Rtl" 

RESIDENT tmp
group by STYLE_ID;

Drop table tmp;