Problem with "sum" with dimension

[michak80]

Hello, 

I have folowing columns:

ORDER	INSTRUCTION
100	PALLETS:  10EUR; 05EUR-N; 01OWA; 00OWA-N; 00CHEP; 00MAX; 00LOM
110	PALLETS:  00EUR; 05EUR-N; 01OWA; 00OWA-N; 00CHEP; 00MAX; 00LOM
120	PALLETS:  06EUR; 03EUR-N; 01OWA; 00OWA-N; 00CHEP; 00MAX; 00LOM
130	PALLETS:  06EUR; 05EUR-N; 01OWA; 00OWA-N; 00CHEP; 00MAX; 00LOM

I prepared sum for each order with following dimetion 

=num(textbetween(INSTRUCTION,'S:','EUR')
+
textbetween(INSTRUCTION,'EUR;','EUR-N')
+
textbetween(INSTRUCTION,'EUR-N;','OWA')
+
textbetween(INSTRUCTION,'OWA;','OWA-N')
+
textbetween(INSTRUCTION,'OWA-N;','CHEP')
+
textbetween(INSTRUCTION,'CHEP;','MAX')
+
textbetween(INSTRUCTION,'MAX;','LOM'))

so, I recieved following table in dimention

ORDER	TOTAL
100	16
110	6
120	10
130	12

I have to prepare table with total qty of pallets. I mean that that result shold be: 

ORDER	TOTAL
4	44

I do not have any problems with ORDERS. But I cannot recieve TOTAL from above tabel. I try to creat master item but it's not visible in measure. 

Could you please help me? 

[rwunderlich]

A couple of tips. First, avid using "+" and use RangeSum() instead. A non-numeric value with "+" renders the entire expression result as null. RangeSum() will tolerate non-numeric and treat that value as zero.

Second, I suggest computing the TOTAL column in the script using Purgechar() and Subfield like this:

LOAD
  ORDER,
  Sum(QTY) as TOTAL
Group By ORDER
;
LOAD
  ORDER,
  SubField(KeepChar(INSTRUCTION, ';0123456789'), ';') as QTY
Resident OrdersTable
;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[marcus_sommer]

In the case that there are more (granular) views wanted - you may to consider to split the information into own records, with something like:

load *, recno() as RecNo, rowno() as RowNo, iterno() as IterNo,
        keepchar(subfield(INSTRUCTION, ';', iterno()), '0123456789') as Value,
        purgechar(subfield(INSTRUCTION, ';', iterno()), '0123456789') as KPI
from Orders while substringcount(INSTRUCTION; ';') + 1 <= iterno();

The counter-fields aren't mandatory else just to visualize the workflow and you may need some further adjustments to trim() and/or to replace() some sub-strings for the final data-set. It will simplify everything within the UI.

[rwunderlich]

@marcus_sommer Wouldn't SubField() be a lot simpler than the while clause?

-ROb

[Chanty4u · Accepted solution]

Try this 

=Sum(Aggr(

  Num(

    textbetween(INSTRUCTION, 'S:', 'EUR') +

    textbetween(INSTRUCTION, 'EUR;', 'EUR-N') +

    textbetween(INSTRUCTION, 'EUR-N;', 'OWA') +

    textbetween(INSTRUCTION, 'OWA;', 'OWA-N') +

    textbetween(INSTRUCTION, 'OWA-N;', 'CHEP') +

    textbetween(INSTRUCTION, 'CHEP;', 'MAX') +

    textbetween(INSTRUCTION, 'MAX;', 'LOM')

  ),ORDER

))

[Lech_Miszkiewicz]

I will just add that num# would be appropriate function instead of num which on this case is pointless.
cheers 

[marcus_sommer]

In general would be subfield() without the while-triggered iterno() as third parameter be working but only for the rather simpler use-cases.

As far as there are more as a single field and/or multiple stages (n while-loops in a row within preceding loads) are included/necessary the while-logic is much more powerful especially if the order-information is needed for further aggregations or associations(joins/mappings and/or to the option to replace crosstable-loads.

Not directly related to the substring-evaluation else the numeric resolution of ranges to dedicated numbers it's my preferred tool against an intervalmatch. Therefore it's my standard-approach for many scenarios, quickly done, well suited to visualize the load-progess in detail and after proving everything worked like expected all not mandatory record-counter are removed or commented to keep the data-model clean and performant.

[michak80]

Thank you all for soutions!