If value exists in resident table, add a new row

[celianou]

Hi everyone,

I have an SQL query to create table A (table of supplier order points) thanks to several joins.

For the suppliers (who are in the supplier table) who do not have an order point (in table A), I would to create a new row in table A, with default information.

I can't find a way to do this... (I'm new to Qlik)

[sunny_talwar]

@celianou Would you be able to share an example with the output you expect to see after running the script?

[celianou]

I'm creating a fake example for you because I can't communicate my data.

Let's say that in table A :

supplier;OrderPoint;Info_OP_1;Info_OP_2
sup1;2;hello;world
sup2;4;hi;you

And now imagine my supplier table :

supplier;info1;info2
sup1;bla;blabla
sup2;Iam;newbie
sup3;at;Qlik

In this table, sup3 is not in table A.
So I want to add sup3 in table A. It would be :

supplier;OrderPoint;Info_OP_1;Info_OP_2
sup1;2;hello;world
sup2;4;hi;you
sup3;defaultnumber;defaultinfo;defaultinfo

Tell me if it's not clear. It's hard for me to explain.

[marcus_sommer · Accepted solution]

You could try something like this:

Order: load supplier, order, info from Order;
concatenate(Order)
load supplier, 'no order' as order, info from Supplier where not exists(supplier);

whereby you mustn't load Supplier already before because exists() refers to already loaded fieldvalues and therefore the order of load-statements (could) have an impact.

- Marcus 

[celianou]

Thank you so much Marcus, it works perfectly !