Defining a top-level table combining several other tables

[yankeybeans]

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?

[RafaelBarrios]

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]

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]

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]

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]

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

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! 🙂