topic Table 1 data into Table 2 as dimension in App Development

Table 1 data into Table 2 as dimension

DEMONIO_AZUL — Fri, 07 Feb 2025 20:51:39 GMT

Hello there,

I have two tables, which have no relationship, but I need to add info from one table into the other as a dimension.

These are my input tables:

and

And this is the desired output table:

The input tables come from different "owners" and have more records, I just need to know how to create the [Desired Output]: at the load script.

If it were only one project, I would have added this:

'NBU' as [Project Acronym]

when loading [Systems Data]:

But since this value [Acronym] can be variable and it comes from a file, I need to read it from [Project Data]: and "append" it to [Systems Data]: somehow. However, since there is no field that can provide a link between the two input tables, no JOIN works.

Any ideas will be appreciated!

Thank you!

Re: Table 1 data into Table 2 as dimension

Vegar — Sat, 08 Feb 2025 14:36:26 GMT

You could try to do a full outer join.

Output:
LOAD * inline [
Name, Acronym
New building,NBU
];

JOIN (Output)
LOAD * inline [
Systems,Scope
Sys1, HVAC
Sys2, ACcess control
Sys3, Water piping
];

Re: Table 1 data into Table 2 as dimension

diegozecchini — Sat, 08 Feb 2025 15:09:00 GMT

Hi!

then one from @Vegar is a perfect solution.

you could use also
// Load Project Data and store the Acronym into a variable
ProjectData:
LOAD
Acronym AS ProjectAcronym
FROM [ProjectData.xlsx]
(ooxml, embedded labels, table is Sheet1);

// Store the Acronym in a variable
LET vProjectAcronym = Peek('ProjectAcronym', 0, 'ProjectData');

// Load Systems Data and add Project Acronym dynamically
SystemsData:
LOAD
'$(vProjectAcronym)' AS [Project Acronym],
Systems,
Scope
FROM [SystemsData.xlsx]
(ooxml, embedded labels, table is Sheet1);

but it works because you have only one project, do you have multiple projects?

Re: Table 1 data into Table 2 as dimension

DEMONIO_AZUL — Mon, 10 Feb 2025 14:55:42 GMT

Thanks, Diego.

What you proposed worked perfectly - the JOIN didn't because there were no common fields and the result was a bigger table with one row empty with only Acronym = RSP.

Re: Table 1 data into Table 2 as dimension

DEMONIO_AZUL — Mon, 10 Feb 2025 14:59:13 GMT

Thanks, but I had already tried the full outer JOIN and it did not work. There are no common fields between the two tables and the JOIN only added a row to [Systems Data]: adding Acronym as another column but the other two fields (Systems, Scope) were empty.

Re: Table 1 data into Table 2 as dimension

diegozecchini — Mon, 10 Feb 2025 15:16:07 GMT

Good! Glad it helped