Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
yankeybeans
Contributor II
Contributor II

Defining a top-level table combining several other tables

I've used three LOAD commands to link three tables (see attached screen shot of Data Model Viewer). This resulting query gives multiple FUND_ACCT_NOs, CHARGE_KEYs, and Total_HRS for each PROJ_NUMBER.

I want to treat this resulting query as though it were a single top-level table.

Then I could then use a LOAD operation on the top-level table, using the RESIDENT command, to sum (Total_HRS) by PROJ_NUMBER (using GROUP BY): giving only one row for each PROJ_NUMBER.

Then I could delete the original three tables (using the drop table command), and link the combined top-level table to an income table that has only one income value per PROJ_NUMBER. 

How do I combine the three initially loaded tables into a single named table?

 

 

Labels (1)
5 Replies
RafaelBarrios
Partner - Specialist
Partner - Specialist

Hi @yankeybeans 

you could use joins to get one single table

https://help.qlik.com/en-US/sense/August2022/Subsystems/Hub/Content/Sense_Hub/Scripting/combine-tabl...

table1:
load *
from table1;

Join(table1)
Load * 
From table2;

Join(table1)
Load * 
From table3;

it will do the joins on same named columns.

depending on you data you will need to use left, right, inner or outer join

another option would be a mapping table

https://help.qlik.com/en-US/sense/August2022/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefix...

 

hope this helps.

best,

Help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 🙂

yankeybeans
Contributor II
Contributor II
Author

Rafael

Regards your joining link, I see this:

      Example:  

         LOAD a, b, c from table1.csv;

           join LOAD a, d from table2.csv;

          The resulting internal table has the fields a, b, c and d.

I can do this using my own LOAD statements; but notice that the website states "The resulting internal table ..."

I need to treat this 'internal table' as its own named table. Do you know how to name and use this internal table?

RafaelBarrios
Partner - Specialist
Partner - Specialist

hi @yankeybeans 

the resulting table will have the name you give to the first table

Using  your example

my_table_name_here:
LOAD a, b, c from table1.csv;
join LOAD a, d from table2.csv;

the resulting table name will have a,b,c,d and will be called my_table_name_here

now you can call your table by using Resident load statement

my_new_table:
load
a,
b,
c,
sum(d) as group_total
Resident my_table_name_here

group by a,b,c;

https://help.qlik.com/en-US/sense/August2022/Subsystems/Hub/Content/Sense_Hub/Scripting/load-data-fr...

and if you don’t need the first one anymore

drop table my_table_name_here;

 

When you want to name a table, you can do what I just did or if you want to use white spaces or special character you need to use [], for example: [My great looking table name]

hope this helps.

best,

Help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 🙂

yankeybeans
Contributor II
Contributor II
Author

Rafael,

I still get error.  When I use your:

  my_table_name_here:
  LOAD a, b, c from table1.csv;
  join LOAD a, d from table2.csv;

I don't get a single table named my_table_name_here; I get two tables: my_table_name_here (which is just table1) and table2. So when I try to do a  'resident' load on my_table_name_here, I only have access to fields in table1.

  

RafaelBarrios
Partner - Specialist
Partner - Specialist

hi @yankeybeans 

try this attaching the files to your app

my_table_name_here:
LOAD
team_1,
team_2
FROM [lib://AttachedFiles/file1.txt]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

join
LOAD
team_1,
team_3
FROM [lib://AttachedFiles/file2.txt]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);


another_new_table:
load
team_1 as new_field_team1,
team_2 as new_field_team2,
team_3 as new_field_team3

RESIDENT my_table_name_here

your model should be this

RafaelBarrios_0-1667253310108.png

i'm attaching a couple of files for you to try this script.

 

Hope this helps.

Best,

Users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 🙂