topic Re: Create one table from 2 existing tables in Connectivity & Data Prep

Create one table from 2 existing tables

Amba — Wed, 22 Dec 2021 14:49:42 GMT

Hey guys, please see the pictures for a better understanding.

What I'm trying to do is create that RevenuesDiscounts table that will contain all information from discounts and from revenues so that I can work with it.

RevenuesDiscounts:
load revenues.amount, 'revenue' as type resident revenues;
load discounts.discount_amount, 'discount' as type resident discounts;

How may I do that so that my script works?

Thank you!

Re: Create one table from 2 existing tables

vunguyenq89 — Tue, 22 Oct 2019 09:35:25 GMT

You can perform a JOIN between the two tables. I usually do this by using JOIN command in Data Load Editor (reload script). However, you can also join tables interactively in Data Manager. Instruction at https://help.qlik.com/en-US/sense/September2019/Subsystems/Hub/Content/Sense_Hub/LoadData/join-keep-tables-in-data-manager.htm

Re: Create one table from 2 existing tables

otmane — Tue, 22 Oct 2019 09:39:40 GMT

Hi Amba,

You should add the "concatenate" prefix in the script between the two load lignes :

You can also add a new field in the loads statements to insert the tables types.

RevenuesDiscounts:
load 'revenues' as type , revenues.amount, 'revenue' as type resident revenues;

concatenate

load 'discounts' as type, discounts.discount_amount, 'discount' as type resident discounts;

Hope This help,

Regards,

Otmane

Re: Create one table from 2 existing tables

Amba — Tue, 22 Oct 2019 11:04:23 GMT

Thank you guys.
Using concatenate doesn't help me and gives me an error message.

RevenuesDiscounts:
load 'revenues' as type , revenues.amount, 'revenue' as type resident revenues;
concatenate
load 'discounts' as type, discounts.discount_amount, 'discount' as type resident discounts;

@vunguyenq89

Using the join would merge the tables together. What I'm looking for is a new table that would contain the two tables. But I want them to still be available alone.
Should be smth like:

Table1 : RevenuesDiscounts(new)
Table2: Revenues(didnt change)
Table3: Discounts(didnt change)

Re: Create one table from 2 existing tables

otmane — Tue, 22 Oct 2019 11:04:25 GMT

This error is not due to the concatanate, it's because you are not using the right table in the resident statement:

"resident revenues". You should remplace i with the right table name that contain revenues data.

Re: Create one table from 2 existing tables

Amba — Tue, 22 Oct 2019 11:10:16 GMT

But both table exist in the data manager, I'm a bit lost 😄

Re: Create one table from 2 existing tables

otmane — Tue, 22 Oct 2019 11:37:51 GMT

Ok, can you provide your script ?

Re: Create one table from 2 existing tables

Amba — Tue, 22 Oct 2019 12:19:03 GMT

I've sent you a copy to your inbox.

So actually all I want to do is have those two fields:

In that new "RevenuesDiscounts" cell. So that I can later compare the Revenues to the Amount of revenues+discount.

I managed to do it but that was quite a long time ago and I have no clue how to do it now

Re: Create one table from 2 existing tables

Amba — Wed, 23 Oct 2019 07:28:00 GMT

I managed to fix it. My script has many sections on the left where I load data from various databases.
Basically the reason why it did not work is that I wanted to load data that was not stored yet since I as executing this script before the Lib Connect.

Sometimes, the answer might be easy as well 😃