Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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
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 ...
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.
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.
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
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.
Hi,
it would be great if you can provide sample data.