Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

jim_chan
Contributor III

How to load in old n new data , and use where not exists?

Hi Guys,

I want o use Where not Exists for my Order and New Order table.

I have loaded Order_Table and I have transformed and added some new field. Then found out i have another New_Order table which vae some new records need to load in as well. So i wrote the script below. But when i reloaded it, it created Syntetci key.

How to avoid that and able to load in both Order and New Order table records?

Orders:

LOAD

EmployeeID &'|' & Year as order_key,

OrderID,

     Month(OrderDate) as Month,

     OrderDate,

     EmployeeID,

     CustomerID,

     ProductID,

     APPLYMAP('Product_Details',ProductID,'ProductID Not Available in Product Details') as Product_Made,

     //APPLYMAP('Product_Details',ProductID,null()) as Product_Made,

     ShipperID,

    Year,

     Sales,

     Cost,

     Quantity   

   

FROM

[Data Source\Order.xlsx]

(ooxml, embedded labels, table is Order);


New_Order:

LOAD OrderID,

     OrderDate,

     EmployeeID,

     CustomerID,

     ProductID,

     ShipperID,

     Year,

     Sales,

     Cost,

     Quantity

FROM

[Data Source\New_Order.xlsx]

(ooxml, embedded labels, table is Order)

where not exists(OrderID);

synthetic_key.jpg

Rgds

Jim

7 Replies
MVP
MVP

Re: How to load in old n new data , and use where not exists?

with concatenate

Orders:

LOAD

EmployeeID &'|' & Year as order_key,

OrderID,

    Month(OrderDate) as Month,

    OrderDate,

    EmployeeID,

    CustomerID,

    ProductID,

    APPLYMAP('Product_Details',ProductID,'ProductID Not Available in Product Details') as Product_Made,

    //APPLYMAP('Product_Details',ProductID,null()) as Product_Made,

    ShipperID,

    Year,

    Sales,

    Cost,

    Quantity 

 

FROM

[Data Source\Order.xlsx]

(ooxml, embedded labels, table is Order);


//New_Order:

concatenate (Orders)

LOAD OrderID,

    OrderDate,

    EmployeeID,

    CustomerID,

    ProductID,

    ShipperID,

    Year,

    Sales,

    Cost,

    Quantity

FROM

[Data Source\New_Order.xlsx]

(ooxml, embedded labels, table is Order)

where not exists(OrderID);


maybe you have to rename some fields in the second load statement to match the fields in the first load

jim_chan
Contributor III

Re: How to load in old n new data , and use where not exists?

Hi Maxgro,

Y cant we use Join instead of using concatenate?

Rgds

Jim

MVP
MVP

Re: How to load in old n new data , and use where not exists?

I think concat is better in your case as you want to add new records to the same table (new orders to order)

You can find an excellent description of the difference here

Understanding Join and Concatenate | Qlikview Cookbook

johncaqc
Valued Contributor

Re: How to load in old n new data , and use where not exists?

I agree with concatenate here too.

Plus, "maybe you have to rename some fields in the second load statement to match the fields in the first load"...

...I think the second table will need the key field "EmployeeID &'|' & Year as order_key", unless you were already referring to this.

hth,

john

jim_chan
Contributor III

Re: How to load in old n new data , and use where not exists?

So... here's what i have did. But I am not sure . is this correct?

Orders:

LOAD

EmployeeID &'|' & Year as order_key,

OrderID,

     Month(OrderDate) as Month,

     OrderDate,

     EmployeeID,

     CustomerID,

     ProductID,

     APPLYMAP('Product_Details',ProductID,'ProductID Not Available in Product Details') as Product_Made,

     //APPLYMAP('Product_Details',ProductID,null()) as Product_Made,

     ShipperID,

    Year,

     Sales,

     Cost,

     Quantity   

   

FROM

[Data Source\Order.xlsx]

(ooxml, embedded labels, table is Order);

//New_Order:

Concatenate(Orders)

LOAD

EmployeeID &'|' & Year as order_key,

OrderID,

  Month(OrderDate) as Month,

     OrderDate,

     EmployeeID,

     CustomerID,

     ProductID,

      APPLYMAP('Product_Details',ProductID,'ProductID Not Available in Product Details') as Product_Made,

     ShipperID,

     Year,

     Sales,

     Cost,

     Quantity

FROM

[Data Source\New_Order.xlsx]

(ooxml, embedded labels, table is Order)

where not exists(OrderID);

It's look the same as the 1st load. and there's no more synthetic key.

jim_chan
Contributor III

Re: How to load in old n new data , and use where not exists?

If i would need to create 2 table box, 1 is to show old record and another is show new record, how should i write it in expression ?

MVP
MVP

Re: How to load in old n new data , and use where not exists?

add a field Order Type and set this field to New Orders  or Old Orders

you can filter new and/or old orders with this new field in the user interface

Community Browser