19 Replies Latest reply: Apr 20, 2016 9:19 AM by Stefan Wühl RSS

    how to write expression

    naveen kumar

      Hello, if i got a sample like this

       

       

      number         code           value old                decision               category                date

      1000                A                                           accepted                    smelting              01/01/2015

      1001                A                   L1                     exception                     smelting               01/01/2015

      1003                A                    L2                    exception                    smelting             01/01/2015

      1004                A0                                           accepted                  smelting              01/01/2015

      1005                 R                                           rejected                    smelting                01/01/2015

       

       

      in a bar chart if i use

      Dimension:      category,decision

      expresssion:  ????????????? what expression do i need to write

      so that to get this o/p

       

       

       

      X-axis in bar chart                         count=        2                   1                        2                   

                                                                      accepted       rejected              exception

       

      expression:i wrote  this ......but the problem is how can i make this A code numbers in to two diff decision count 

       

      =count({$<Category={'smelting'},ROLE=,SUPCAT=,MaterialName=,YEAR=,QUATERS=,name1=,date={">=$(vFromdate)<=$(vTodate)"}>}number)

        • Re: how to write expression
          Sunny Talwar

          naveen kumar wrote:

           

          expression:i wrote  this ......but the problem is how can i make this A code numbers in to two diff decision count 

          What do you mean when you say two diff decision count?

            • Re: how to write expression
              AC BC

              When you are giving dates from and to also input in set analysis how can get the count as you expected Naveen

              • Re: how to write expression
                naveen kumar

                HI,

                I mean if you see code A as two decision

                                         1)accepted

                                          2)exception

                so i want differentiate the numbers,

                like code=A       count =1 for accepted

                       code=A      count =2 for exception

                  • Re: how to write expression
                    Sunny Talwar

                    But code is not even one of your dimension. I think you should get 2, 1, 2 as the result of your expression. Are you not getting it?

                      • Re: how to write expression
                        naveen kumar

                        i think i am completely wrong with  what i said

                        Actually the problem is ...

                        if i got data like this


                        table1:

                        number     valueold             code           

                        1000                                     A

                        1001           L1                      A

                        1003                                     R

                        1004                                     A0



                        table2:

                        code                decision

                        A                      accepted

                        A0                      accepted

                        A                          exception

                        R                         rejected



                        so if i join these two i am like


                        number                code               decision

                        1000                        A                  accepted

                        1000                         A                  exception

                        1001                           A             accepted

                        1001                          A                 excception

                        1003                            R                    rejected

                        1004                            A0                   accepted


                        which  is wrong


                        i want like this

                        number                   code                   decision

                        1000                        A                  accepted

                        1001                          A                 excception

                        1003                            R                    rejected

                        1004                            A0                   accepted



                        these that valueold field based up on that i need to differentiate the codes and there numbers

                        but i am not sure whether to differentiate it at script or at expression level










                          • Re: how to write expression
                            Stefan Wühl

                            Use a mapping approach instead of a JOIN:

                             

                            MAP:

                            MAPPING

                            LOAD * INLINE [

                            code,                decision

                            A ,                     accepted

                            A0,                      accepted

                            L1A,                          exception

                            R,                         rejected

                            ];

                             

                            LOAD

                            number,     valueold,             code,

                            ApplyMap('MAP', valueold&code, 'no Map') as decision

                            ...

                            FROM

                              • Re: how to write expression
                                naveen kumar

                                To elaborate a bit more ,

                                while checking each product(number) the end user gives the rating (code)

                                i mean some products which were directly accepted (code=A)

                                and some products which were not accepted first(valueold =L1) then a later if same product get accepted  which comes under exception accepted(code =A)...so the client want to see the count which were directly accepted and which were indirectly accepted  i mean (exception count)

                                  • Re: how to write expression
                                    Stefan Wühl

                                    Naveen Kumar,

                                     

                                    I am not sure what you want to tell me. That the mapping approach doesn't work?

                                     

                                    I do get this with my code posted above:

                                     

                                    number valueold code decision
                                    1000 Aaccepted
                                    1001L1Aexception
                                    1004 A0accepted
                                    1003 Rrejected

                                     

                                     

                                    If it doesn't work with your real data, then please take a little time to prepare a more complete sample application (that should be reloadable) and attach this to your thread.

                                     

                                    Please consider that also (our) time is a limited ressource.

                                      • Re: how to write expression
                                        naveen kumar

                                        exactly,  i am looking for the same o/p ....but only the thing is i got three diff  tables and i am not sure on which table do i need to use this apply map  ,i got struck at that plz will you suggest me ... 

                                         

                                        table1

                                        qave:

                                        LOAD     PRUEFLOS,  (which is number )           

                                                       VCODE            (which is code)

                                        FROM

                                        [C:\qlikview\QVDS\Decision.qvd]

                                        (qvd);

                                         

                                         

                                        Left JOIN(qave)

                                        CDPOS:

                                        LOAD           OBJECTID as PRUEFLOS,            (which is number )   

                                                              VALUE_OLD                                  (which is valueold)

                                        FROM

                                        [C:\qlikview\QVDS\CDPOS.qvd]

                                        (qvd);

                                         

                                         

                                        LEFT JOIN(qave)

                                        LOAD                      VCODE,                                      (which is code)

                                                                         VALUE_OLD,                               (which is valueold)

                                                                         Decision                                           

                                          

                                        FROM

                                        [C:\qlikview\QVDS\VCodeMapping.xlsx]

                                        (ooxml, embedded labels, table is Sheet1);

                                         

                                         

                                         

                                         

                                          • Re: how to write expression
                                            Stefan Wühl

                                            Something like this

                                             

                                            MAP:

                                            MAPPING LOAD

                                            VALUE_OLD&VCODE, Decision;

                                            LOAD                      VCODE,                                      //(which is code)

                                                                            VALUE_OLD,                            //  (which is valueold)

                                                                            Decision                                         

                                             

                                            FROM

                                            [C:\qlikview\QVDS\VCodeMapping.xlsx]

                                            (ooxml, embedded labels, table is Sheet1);

                                             

                                             

                                             

                                            qave:

                                            LOAD    PRUEFLOS,  //(which is number )      

                                                          VCODE        //  (which is code)

                                            FROM

                                            [C:\qlikview\QVDS\Decision.qvd]

                                            (qvd);

                                             

                                             

                                            Left JOIN(qave)

                                            CDPOS:

                                            LOAD          OBJECTID as PRUEFLOS,            //(which is number ) 

                                                                  VALUE_OLD                                //  (which is valueold)

                                            FROM

                                            [C:\qlikview\QVDS\CDPOS.qvd]

                                            (qvd);

                                             

                                             

                                            RESULT:

                                            LOAD *,

                                                ApplyMap('MAP', VALUE_OLD & VCODE, 'No Mapping found') as Decision

                                            RESIDENT qave;

                                             

                                            DROP TABLE qave;

                                      • Re: how to write expression
                                        naveen kumar

                                        Hello, if i got script like

                                         

                                        qave:

                                        LOAD PRUEFLOS,

                                             VCODE

                                        FROM

                                        [C:\qlikview\QVDS\Decision.qvd]

                                        (qvd);

                                         

                                         

                                        Left JOIN(qave)

                                        CDPOS:

                                        LOAD OBJECTID as PRUEFLOS,

                                             VALUE_OLD

                                        FROM

                                        [C:\qlikview\QVDS\CDPOS.qvd]

                                        (qvd);

                                         

                                         

                                        LEFT JOIN(qave)

                                        LOAD VCODE,

                                             VALUE_OLD,

                                             Decision

                                          

                                        FROM

                                        [C:\qlikview\QVDS\VCodeMapping.xlsx]

                                        (ooxml, embedded labels, table is Sheet1);

                                         

                                         

                                        then

                                         

                                      • Re: how to write expression
                                        Sunny Talwar

                                        Try this may be:

                                         

                                        table1:

                                        LOAD *,

                                          AutoNumber(number, code&'1') as Key;

                                        LOAD * Inline [

                                        number,    valueold,            code         

                                        1000,                ,                    A

                                        1001,          L1,                      A

                                        1003,              ,                      R

                                        1004,              ,                      A0

                                        ];

                                         

                                        table2:

                                        LOAD *,

                                          AutoNumber(decision, code&'2') as Key;

                                        LOAD * Inline [

                                        code,                decision

                                        A,                      accepted

                                        A0,                      accepted

                                        A,                          exception

                                        R,                        rejected

                                        ];

                                         

                                        Join (table1)

                                        LOAD *

                                        Resident table2;

                                         

                                        DROP Table table2;


                                        Capture.PNG

                                          • Re: how to write expression
                                            Sunny Talwar

                                            I guess, I did not read through. Although in this particular case my script might be working, but Stefan's script is more scale-able as it meets the requirement of oldvalue

                                            • Re: how to write expression
                                              naveen kumar

                                              Hello, if i got script like

                                               

                                              qave:

                                              LOAD PRUEFLOS,

                                                   VCODE

                                              FROM

                                              [C:\qlikview\QVDS\Decision.qvd]

                                              (qvd);

                                               

                                               

                                              Left JOIN(qave)

                                              CDPOS:

                                              LOAD OBJECTID as PRUEFLOS,

                                                   VALUE_OLD

                                              FROM

                                              [C:\qlikview\QVDS\CDPOS.qvd]

                                              (qvd);

                                               

                                               

                                              LEFT JOIN(qave)

                                              LOAD VCODE,

                                                   VALUE_OLD,

                                                   Decision

                                                 

                                              FROM

                                              [C:\qlikview\QVDS\VCodeMapping.xlsx]

                                              (ooxml, embedded labels, table is Sheet1);

                                               

                                               

                                              then

                                              • Re: how to write expression
                                                naveen kumar

                                                Hello sunny,

                                                where to use apply map in this script

                                                 

                                                table1

                                                qave:

                                                LOAD     PRUEFLOS,  (which is number )          

                                                               VCODE            (which is code)

                                                FROM

                                                [C:\qlikview\QVDS\Decision.qvd]

                                                (qvd);

                                                 

                                                 

                                                Left JOIN(qave)

                                                CDPOS:

                                                LOAD           OBJECTID as PRUEFLOS,            (which is number )  

                                                                      VALUE_OLD                                  (which is valueold)

                                                FROM

                                                [C:\qlikview\QVDS\CDPOS.qvd]

                                                (qvd);

                                                 

                                                 

                                                LEFT JOIN(qave)

                                                LOAD                      VCODE,                                      (which is code)

                                                                                 VALUE_OLD,                               (which is valueold)

                                                                                 Decision                                          

                                                 

                                                FROM

                                                [C:\qlikview\QVDS\VCodeMapping.xlsx]

                                                (ooxml, embedded labels, table is Sheet1);

                                      • Re: how to write expression
                                        Stefan Wühl

                                        I agree, I don't understand completely. You have one Count() expression, so maybe

                                         

                                        =count({$<Category={'smelting'},ROLE=,SUPCAT=,MaterialName=,YEAR=,QUATERS=,name1=,date={">=$(vFromdate)<=$(vTodate)"}, code = {A} >}number)

                                        • Re: how to write expression
                                          pradeep t

                                          edit: insert the image

                                          for the bellow dimension & expressions, you will get the result as you expected..

                                          Dimensions: category,decision

                                          Expression: Count(number)

                                          Test.png

                                          • Re: how to write expression
                                            Nico Manro Oberholzer

                                            Naveem,

                                             

                                            Your Set analysis should work as explained by the other community members.

                                             

                                            My logic would be:

                                            The graph's dimension = CODE

                                            The expression1 [Exception Items] =count({$<Category={'smelting'},decision = {'exception'}>}number)

                                            The expression2 [Accepted Items] =count({$<Category={'smelting'},decision = {'accepted'}>}number)

                                            The expression3 [Rejected Items] =count({$<Category={'smelting'},decision = {'rejected'}>}number)

                                             

                                            Leave out the date={">=$(vFromdate)<=$(vTodate)" for now, because if your date formats aren't the same in the Variables and in the tables then you will get null values anyway. Check to see if you get the desired results then build in the dates.

                                             

                                            Just a FYI: Hard-coding the scenario into the graph with SET ANALYSIS, is not always ideal. If the data structure changes or more CODES | DECISIONS are added into the data, then they will be neglected, because these have been hard-coded.

                                             

                                            IF you can't win, share the QVW into the thread, i'm sure we'll figure it out.

                                             

                                            Nico