Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

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

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
1 Solution

Accepted Solutions
Highlighted
Master II
Master II

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

Learning never stops.

View solution in original post

1 Reply
Highlighted
Master II
Master II

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

Learning never stops.

View solution in original post