Script

[louise119]

Hi, how can I write a script to create a new table and take items from the currently loaded table and do the calculations?

I want to calculate the difference between dataA in table A and dataB in table B and create a new table with that item. How should I write the script?

[MayilVahanan]

Hi

You need to join the 2 tables into one table with required fields with "Resident" load and do the calculation based on ur requirement.

ex:

Temp:

Load DataA, Keyfield resident TableA;

Join

Load DataB, Keyfield resident TableB;

Load Keyfield, DataA-DataB as DataC resident Temp;

Drop table Temp;

[louise119]

Thank you so much.
Does "Drop table Temp;" mean that the table Temp is not created?

I want to create the new table with the value I calculated.

[MayilVahanan · Accepted solution]

It will drop the intermediate table.

Here, FinalTable will be available in ur data model.

ex:

Temp:

Load DataA, Keyfield resident TableA;

Join

Load DataB, Keyfield resident TableB;

Finaltable:

Load Keyfield, DataA-DataB as DataC resident Temp;

Drop table Temp;

[louise119]

Thank you so much!

What do you mean "Keyfield"?

[MayilVahanan]

hi

I think, you're trying to understand Qlik.

KeyField is common field between tables.

Data C is the new field after subtract Data A and Data B.

[louise119]

If the shared fields of table A and table B are A.date and B.date respectively, will it be as follows?
what about the red part?

ex)
Temp:
Load DataA, A.date resident TableA;
Join
Load DataB, B.date resident TableB;

Finaltable:
Load Keyfield, DataA-DataB as DataC resident Temp;

Drop table Temp;

[MayilVahanan]

HI 

Can you mention the common fields between the tables and also share the data model to understand better

[louise119]

I want to create the new table which has (Sales.Invoice Date - Item_master.Date).

 

 

[MayilVahanan]

Hi

Actually, you can see tables via data model viewer.. It will give clear picture.

I think, common fields between 2 tables is "itemNumber". If so, in ur case, Itemnumber is the key field.

And also, for your scenario, you can use Mapping and ApplyMap also. 

Like

MapTable:

Mapping Load ItemNumber, Date resident ItemMaster;

Load *, ApplyMap('MapTable', ItemNumber, 'NA') as ItemMasterDateValue from yoursalestable;