Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
michak80
Contributor III
Contributor III

Problem with "sum" with dimension

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? 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Labels (2)
1 Solution

Accepted Solutions
Chanty4u
MVP
MVP

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

))

View solution in original post

7 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

-ROb

Chanty4u
MVP
MVP

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
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
Contributor III
Contributor III
Author

Thank you all for soutions!