Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi comm,
so i want to count articles by their numbers(art_nr) when two columns have the same value.
But these two columns are in two different tables and i want to group them by on of these columns in table.
So we habe table 1 with the articles like
ordertag status
1 123 20
2 123 20
3 123 70
4 234 20
5 35 20
and then we have an other table with the articleboxes
order boxcode
1 1 (01)23(21)234
2 1 (01)23(21)123
3 2 (01)13(21)35
So i want to count in table like this when their status is under 70
order
1 (01)23(21)234 1
1 (01)23(21)123 2
2 (01)13(21)35 1
So when the tag and the end of the boxcode are the same i want to count on the boxcode
thoughed about count(), if(), aggr() and set-analysis but didnt get it to work.
Also im working on getting the tag out of the boxcode as an extra field with left() in load script.
But the length of the tag is not fix but the boxcode before the tag is fix.
Maybe someone can help.
so i tried a bit and foudn out that this will give me the tag out of the boxcode:
mid("parent_element_string",23) as parent_ipactype,
Hi Eric
LOAD * Inline [
tag, status
123, 20
123, 20
123, 70
234, 20
35, 20
];
Join
LOAD SubField(boxcode,')',-1) as tag,boxcode Inline [
boxcode
(01)23(21)234
(01)23(21)123
(01)13(21)35
];
Regards,
Antonio
thx for ur answer but I think i need to go more into the materia.
Ur Solution works right but is not what I exectly wanted.
So I have an order that has multiple tags.
So I have a table with a drilldown masterelement where I can drill down from order to tag.
Now I want to sum the articles from another table with the short form of the boxcode to map to the tags in the table.
Cause with ur solution it sums for an order with no problem but when i rill down to the tag both tag get the same complete sum.
Sorry
Where is 'Order' in Your sample Data ? Can You complete you sample ?
Ohhh sorry.
so we have
order | ordertag | shouldvalue
and then u have the article table with
art_nr | boxcode | shortboxcode(=ordertag)
and each row in this article table is 1 article for the comparisen
so in the end i want the table in qlik sense to be
order | ordertag | is(summ of articles | shouldvalue | %
remember the order and ordertag is a drilldown dimension.
So to clear all things up.
we have a table called article where we have all articles stored with following design
id order_id article long_string short_tag (from long_string)
1 1 a (01)24234(21)234 234
2 1 b (01)24234(21)114 114
3 1 a (01)11111(21)234 234
4 2 c (01)66666(21)111 111
5 3 f (01)55554(21)333 333
and we have the ordertable with
order_id boxcode amount(should)
1 1 234 2
2 1 114 1
3 2 111 2
4 3 333 1
and we add these to columns in the dashboard
is(sum from article) %(is/amount*100)
2 100
1 100
1 50
1 100
hope this can clear things up sorry for my bad english and the confusion
so sorry for all this!
I Think i found a solution that works:
aggr(count(if(status<70,art_nr)),order_id)
Due to the drill down dimension of the chart i get the sorting to the specific boxcode with the order_id.
seem to work but i ll mark this answer as correct when im 100% sure.