Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

count when two columns (two different table) have same value

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.

7 Replies
Not applicable

Re: count when two columns (two different table) have same value

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,

antoniotiman
Honored Contributor III

Re: count when two columns (two different table) have same value

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

Not applicable

Re: count when two columns (two different table) have same value

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

antoniotiman
Honored Contributor III

Re: count when two columns (two different table) have same value

Where is 'Order' in Your sample Data ? Can You complete you sample ?

Not applicable

Re: count when two columns (two different table) have same value

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.

Not applicable

Re: count when two columns (two different table) have same value

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

Not applicable

Re: count when two columns (two different table) have same value

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.

Community Browser