join tables correctly

[BahnMensch]

Hello together,

I try to join a table with a crosstable, but somehow it doesn't work properly.

This is my script:

Table1:

Crosstable(Business_Year, Quantity,9)

LOAD "PSP-Dokument" as PSP, Projekt, ...., "BY 2014", "BY 2015", ...; FROM ...

Left join Table2:

LOAD ... as PSP, Komponente, ... FROM ...

What I try to do or want to have:

I want Table2 as my main table and want to join it with the crosstable. BUT I want only the Data from the crosstable, which is included in Table2. The other informations shouldn't be displayed.

What I get:

Every information from the crosstable and different content in the PSP datafield.

I tried to solve it in different ways, such as:

Table 2:

LOAD ... as PSP, Komponente, ... FROM ...

Left join Table1:

Crosstable(Business_Year, Quantity,9)

LOAD "PSP-Dokument" as PSP, Projekt, ...., "BY 2014", "BY 2015", ...; FROM ...

But then I get the error message: "Illegal combination of prefixes".

Can someone help me or do someone have a suggestion, how to solve it?

Thanks in advance.

[chrismarlow]

Hi,

Maybe re-structure so the join is done after the crosstable & then the table is dropped? Something like;

[Table 2]:
Load * inline [
PSP
1
];

[Table 1]:
crosstable(Business_Year, Quantity,1)
Load * inline [
PSP, 2021, 2020
1, 10, 3
2, 15, 6
];

Left Join ([Table 2])
Load * resident [Table 1];

Drop Table [Table 1];

Cheers,

Chris.

[BahnMensch]

Hey Chris,

thanks for your solution proposal. Is this 'INLINE' for tables without the 'FROM' expression?

I tried it without the inline and if I do it like this way, then all the fields are shown empty, which is not the case. With right join I can see everything ..., but I want to display only the things, which are included in both of them.

[marcus_sommer]

What about to use a right join?

Table1:

Crosstable(Business_Year, Quantity,9)

LOAD "PSP-Dokument" as PSP, Projekt, ...., "BY 2014", "BY 2015", ...; FROM ...

right join Table2:

LOAD ... as PSP, Komponente, ... FROM ...

- Marcus

[ramchalla]

Hi BahnMensch,

Please use the below script. We dont need to specify Table2 after left join.

Table1:

Crosstable(Business_Year, Quantity,9)

LOAD "PSP-Dokument" as PSP, Projekt, ...., "BY 2014", "BY 2015", ...; FROM ...

Left join

LOAD ... as PSP, Komponente, ... FROM ...

[Der_Unbekante_234]

If I use a right join, then I can see only one data in the field and the rest fields are empty from Table 1.

[Der_Unbekante_234]

Hello Ramchalla, 

thanks for your solution proposal. If I do a left join without specify Table 2, then I see everything and the data field PSP has different data, which is not true so. 

[marcus_sommer]

This shouldn't happens and indicates that something is wrong - maybe with the key-field or there was a mistake within your validate-check or ...

Beside this it might be more suitable to make the crosstable-transformation within a temporary table and loading then resident from there.

- Marcus

[chrismarlow]

Hi,

The Inline is trying to produce a toy application that 'works'.

So you should be able to replace the lines that start Load * Inline [ and end ]; with your original load statements (you might need to change the 1 at the end of the crosstable to 9 as you had originally).

Cheers,

Chris.

[ramchalla]

Hi,

it would be great if you can provide sample data.