Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi @yankeybeans
you could use joins to get one single table
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
hope this helps.
best,
Help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 🙂
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?
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;
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! 🙂
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.
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! 🙂