7 Replies Latest reply: Jun 29, 2017 5:11 AM by Eric Hannert RSS

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

    Eric Hannert

      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.

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

          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,

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

            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

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

              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

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

                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.