Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi QV Team,
Can you help me to total only the maximum values?
Please see the attached excel sheet for more details.
Hi
The maximum values are subtotals. You should not load these, rather let QV calculate the totals. Or load only the subtotals and not the detail if that is what you require. What you should not do is load both as every expression in your data model will be more complex and (if your data set is large) may perform very badly.
This script will exclude the subtotals:
Pre:
LOAD [Sales Order],
[Customer ID],
[Serial Number],
[Net Value],
RowNo() As RowID
FrOM
(ooxml, embedded labels, table is Sheet1, filters(
Replace(1, top, StrCnd(null)),
Replace(2, top, StrCnd(null)),
Replace(3, top, StrCnd(null))
));
Final:
NoConcatenate
LOAD [Sales Order],
[Customer ID],
[Serial Number],
[Net Value],
RowID
Resident Pre
Where [Sales Order] = Previous([Sales Order])
AND [Customer ID] = Previous([Customer ID])
AND ([Serial Number]) = Previous ([Serial Number])
Order By RowID DESC;
DROP Table Pre;
Now you can use display the data in a straight table with [Sales Order], [Customer ID], [Serial Number] as dimensions and Sum([Net Value]) in an expression.
HTH
Jonathan
Hi Mrunali,
U can achieve these in backend script as :-
Load max(Net_Value) as MaxNetValue,
SalesOrder,
etc
... group by SalesOrder,...
Regards,
Mukesh
Hi Mukesh,
It is not working. Can you upload .Qvw file ?
Could you please explain your requirement
Sales Order | Customer ID | Serial Number | Net Value |
10200001 | 20 | 3100123 | 0 |
23 | |||
344 | |||
33 | |||
667 | |||
111 | |||
3212 |
On which basis 3212 value would come ?
That is each and every Part.No Values(Rates) based on Serial Number.
Hi
The maximum values are subtotals. You should not load these, rather let QV calculate the totals. Or load only the subtotals and not the detail if that is what you require. What you should not do is load both as every expression in your data model will be more complex and (if your data set is large) may perform very badly.
This script will exclude the subtotals:
Pre:
LOAD [Sales Order],
[Customer ID],
[Serial Number],
[Net Value],
RowNo() As RowID
FrOM
(ooxml, embedded labels, table is Sheet1, filters(
Replace(1, top, StrCnd(null)),
Replace(2, top, StrCnd(null)),
Replace(3, top, StrCnd(null))
));
Final:
NoConcatenate
LOAD [Sales Order],
[Customer ID],
[Serial Number],
[Net Value],
RowID
Resident Pre
Where [Sales Order] = Previous([Sales Order])
AND [Customer ID] = Previous([Customer ID])
AND ([Serial Number]) = Previous ([Serial Number])
Order By RowID DESC;
DROP Table Pre;
Now you can use display the data in a straight table with [Sales Order], [Customer ID], [Serial Number] as dimensions and Sum([Net Value]) in an expression.
HTH
Jonathan