creating new table from columns of different tables

[saikripa]

I have 3 tables and I want to create a 4th table.

table 1 fields:

orderId, code, table1Date, amount, active

table 2 fields:

orderId, code, table2Date, products, active

table 3 fields:

orderId, code, table3Date, customer, status

I want to create table 4 that has the below fields:

orderId, code, Date, table (from which table).

the first 3 table I have loaded as csv files. I am not sure how to write script to create table 4. please help

[mato32188]

Hi @saikripa,

Final:

load

orderId,

code,

table1Date as Date,

amount,

active,

'Table 1' as Table

from your first csv;

load

orderId,

code,

table2Date as Date,

amount,

active,

'Table 2' as Table

from your second csv;

load

orderId,

code,

table3Date as Date,

amount,

active,

'Table 3' as Table

from your third csv;

BR

m

[Dalton_Ruer]

If your data is literally loaded as you have it, you will have synthetic key2 because you have orderid, code both in common among the 3 tables, and also have the field active in common in 2 of the tables. You should ALIAS the fields in the load script to ensure you know it's Table1 Active, Table2 Active etc. 

If your OrderID and Code need to be combined to make a unique key you should combine them using the & operator in the load script. 

Unlike with SQL, you wouldn't need to merge the data together. You could filter any value from any table and the rest of the data would be associated. If you wanted to see the Table1 Active value in an on screen table and the Table2 Active value simply drop both fields in the table object on the screen. The Qlik Associative Model will do the rest. 

If you do want a cleaner data model, your use case seems like the perfect reason to simply JOIN Table 2 and Table 3 data to Table 1 in the load script. You can then have a single row for each orderid or order id and code combination.