two fields in two tables as keyfields

[#1]

Hi all,

i have two tables

Table1

ReferenceID, Outlet, Asset, Sales

Table2

ReferenceID, Outlet, Questions

i have 4 outlets an in every outlet exists the referenceID 256456.

so only the combination of the two fields could give an exact value.

my Problem is that i have in the second table the same Problem with the referenceid and the outlet.

so what can i do to connect These two tables and the Output is exact one value

referenceID, Outlet, Asset, Sales, Questions

thanx

[awhitfield]

Hi Andreas,

you can combine the referenceid and outlet into a single combined key, e.g

LOAD ReferenceID &'-'&Outlet as Unique_ID,
//ReferenceID,
// Outlet,
  Asset,
Sales
FROM

(ooxml, embedded labels, table is Sheet1);

LOAD ReferenceID &'-'&Outlet as Unique_ID,
//ReferenceID,
  //Outlet,
  Questions
FROM

(ooxml, embedded labels, table is Sheet2);

HTH

Andy

[jsanchezh]

You can join both tables in a single one:

Table1:

Load     ReferenceID,

            Outlet,

            Asset,

            Sales

From ...;

Left join (Table1) Load ReferenceID,

                                 Outlet,

                                 Question

From:....;

Good luck!

[#4]

sorry, but i tried this but it doesnt works,... have u an got an idea?

[awhitfield]

No problem,

can you post your qvw?

Andy

[#6]

Hi Andreas,

You can create a Combination Key / Fieldusing RefernceID and Outtlet , which will hold all the combinations of the Values in it.

The Syntax would go like Andy has described in his reply

ReferenceID &'-'&Outlet as Unique_I ' 

You can Use teh Hash128() function on the Unique_ID field .

Hope it helps.

Cheers !

[#7 · Accepted solution]

ok i tried The join no result, can the mistake in the Formate of the field 000.000,00 instead of 00000  ??

[anbu1984]

May be you might have different cases or may be because of space. Try this

LOAD Upper(Trim(ReferenceID) &'-'&Trim(Outlet)) as Unique_ID

[#9]

thanx the two alternatives wit the field combination or the join of the two tables are right, my prob was that the Format of the second table was txt and not numeric.

thanx

[awhitfield]

Nice one!

Andy