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

[Anonymous]

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_NUMBER	PART_NUMBER	PART_TYPE	NET_VALUE
100	System_Part_1	System	500
100	Education_Part_1	Education	100
100	Education_Part_1	Education	-100
200	System_Part_1	System	800
200	Education_Part_4	Education	300
300	System_Part_1	System	900
400	System_Part_1	System	700
400	System_Part_2	System	650
500	System_Part_1	System	495
500	Education_Part_7	Education	150

What I am trying to achieve:

SALES_ORDER_NUMBER	PART_NUMBER	PART_TYPE	NET_VALUE	EDUCATION_STATUS
100	System_Part_1	System	500	No Education Included
100	Education_Part_1	Education	100	No Education Included
100	Education_Part_1	Education	-100	No Education Included
200	System_Part_1	System	800	Education Included
200	Education_Part_4	Education	300	Education Included
300	System_Part_1	System	900	No Education Included
400	System_Part_1	System	700	No Education Included
400	System_Part_2	System	650	No Education Included
500	System_Part_1	System	495	Education Included
500	Education_Part_7	Education	150	Education Included

[pradosh_thakur · Accepted solution]

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