1 Reply Latest reply: Oct 17, 2017 12:38 PM by pradosh thakur RSS

    Find when a value doesn't exist OR if it net value equals 0

    Ben Gardon
    Ben Gardon Oct 17, 2017 11:43 AM

      I have a spreadsheet containing Sales Orders that are made up of part numbers for new systems purchased and part numbers for the education purchased to train that new system. I want to be able to identify those Sales Orders where there is no education included. There are 2 scenarios when this can occur:

      1). If there was no Education included in the order at all. In which case there are no education part numbers showing up against that Sales Order.

      2). If Education was originally included but was then removed. This is represented as 2 separate rows in the spreadsheet. The first shows a positive value and the second shows a negative value with the net result equaling zero.

      I want to be able to create a field in the spreadsheet that can identify these 2 scenarios and fill in the value "No Education Included" and for all other use the field "Education Include".

      Below I have included 2 tables. The first demonstrates an example of what I have now and the second is what I am trying to achieve. Please note that the Part Number names vary greatly and the ones included in the table below are just examples. Any help would be greatly appreciated.

       

      What I have:

      SALES_ORDER_NUMBERPART_NUMBERPART_TYPENET_VALUE
      100System_Part_1System500
      100Education_Part_1Education100
      100Education_Part_1Education-100
      200System_Part_1System800
      200Education_Part_4Education300
      300System_Part_1System900
      400System_Part_1System700
      400System_Part_2System650
      500System_Part_1System495
      500Education_Part_7Education150

       

       

      What I am trying to achieve:

      SALES_ORDER_NUMBERPART_NUMBERPART_TYPENET_VALUEEDUCATION_STATUS
      100System_Part_1System500No Education Included
      100Education_Part_1Education100No Education Included
      100Education_Part_1Education-100No Education Included
      200System_Part_1System800Education Included
      200Education_Part_4Education300Education Included
      300System_Part_1System900No Education Included
      400System_Part_1System700No Education Included
      400System_Part_2System650No Education Included
      500System_Part_1System495Education Included
      500Education_Part_7Education150Education Included
        • Re: Find when a value doesn't exist OR if it net value equals 0
          pradosh thakur
          pradosh thakur Oct 17, 2017 12:38 PM (in response to Ben Gardon)

          script like this . In table A give your table address so it will work

           

           

          A:

           

           

          LOAD SALES_ORDER_NUMBER,

               PART_NUMBER,

               PART_TYPE,

               NET_VALUE

          FROM

          [https://community.qlik.com/thread/278334]

          (html, codepage is 1252, embedded labels, table is @1);

           

           

          //NoConcatenate

           

           

           

           

          left join

           

           

           

           

          LOAD

          SALES_ORDER_NUMBER,

             

               if(sum(NET_VALUE)>0,'EDUCATION included ','NOT included') AS ABC

             

               Resident A

               WHERE PART_TYPE='Education'

               GROUP BY SALES_ORDER_NUMBER

              

               ;

           

           

           

           

          NoConcatenate   

          load

               SALES_ORDER_NUMBER,

               PART_NUMBER,

               PART_TYPE,

               NET_VALUE,

               IF(TRIM(LEN(ABC))>0,ABC,'NOT included') AS ABC

             

               Resident A;

             

            DROP TABLE A;

             

           

           

          edit: This is a qlikview script as i needed to change settings to load webfiles to qliksense . please adjust as per your requirement

          regards

          Pradosh