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

[jim_chan]

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);

Rgds

Jim

[maxgro]

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]

Hi Maxgro,

Y cant we use Join instead of using concatenate?

Rgds

Jim

[maxgro]

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

[johnca]

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]

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]

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 ?

[maxgro]

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