Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
))
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
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.
@marcus_sommer Wouldn't SubField() be a lot simpler than the while clause?
-ROb
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
))
I will just add that num# would be appropriate function instead of num which on this case is pointless.
cheers
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.
Thank you all for soutions!