Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
i searched in the forum but i didn't find anything to solve my problem.
First of all i have three different data sources let's named them Source 1, Source 2 and Source 3.
Source 1 and Source 3 are databases from an ERP-System (the same ERP-System) and Source 2 is a Excel-Sheet.
Next the Source 1 has different tables (names of the table are the same in Source 1 and Source 3 but in following i name Customer in A and B to point out that they are different also the same but different are ZNR. and YNr.).
The table 1 arise from a header and a footer table with the composit key number (ANr.) and Typ.
Also table 3 arises in the same way with header and footer table.
A important aspect is that the source 2 (Exel-Sheet) has only a couple of links (numbers) in the table.
Not all numbers from table 1 and table 3 are in the table 2.
In the following a detail of the tables.
Table 1
ZNr. ANr. TYP Price
10 1 A 100,00 €
20 2 A 200,00 €
30 50 B 100,00 €
Table 2
HNr. ANr. BNr.
1 1
1 3
20 2
Table 3
YNr. BNr. Typ Price
10 1 A 100,00 €
20 3 C 200,00 €
40 2 B 300,00 €
So now its time to explain.
ZNr. is a receipt number (from data source 1)
YNr. is a receipt number (from data source 3)
ANr. is a customer number (from data source 1)
BNr. is a customer number (from data source 3)
HNr. is a head number for ANr. and BNr (represents one costumer with different customer numbers).
ANr and Typ build a key as well as BNr and Typ.
Price is the price.
So the idea was to link the table 1 and 3 to one table.
The Following table is a detail what we want to build with Qlik Sense.
HNr. ANr. BNr. ZNr. YNr. Typ Price
1 1 100 A 100,00 €
1 3 20 C 200,00 €
20 2 40 B 300,00 €
50 30 B 100,00 €
I tried to concatenate the table 1 and table 2 and it worked (I thougth).
If i tried to join source 3 it only screens in the diagramm the prices from source 3.
I tried many things but don't get the rigth form.
I tried for example:
Load ZNr.,
ANr. as %KeyCostumer,
typ,
price
from table 1
Concatenate (table 1)
Load YNr.,
BNr. as %KeyCostumer,
typ,
price
from table 2
I my prospects the problem is that if I arises the table 3 the join (or concatenate) are getting the wrong position.
In SQL i can say something like this:
SELECT ANr, BNr.,Price
FROM table1, table 3
INNER JOIN Customers ON ANr.=BNr.;
But in Qlik Sense the join statement is without ON...
I apologize in advance for my english communication skills
I hope somone can help me/us. Thanks a lot for reading...