topic Re: two fields in two tables as keyfields in QlikView

two fields in two tables as keyfields

Tue, 12 May 2015 09:33:27 GMT

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

Re: two fields in two tables as keyfields

awhitfield — Tue, 12 May 2015 09:48:28 GMT

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

Re: two fields in two tables as keyfields

jsanchezh — Tue, 12 May 2015 09:52:43 GMT

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!

Re: two fields in two tables as keyfields

Tue, 12 May 2015 09:54:08 GMT

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

Re: two fields in two tables as keyfields

awhitfield — Tue, 12 May 2015 10:01:17 GMT

No problem,

can you post your qvw?

Andy

Re: two fields in two tables as keyfields

Tue, 12 May 2015 10:01:34 GMT

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 !

Re: two fields in two tables as keyfields

Tue, 12 May 2015 10:05:18 GMT

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

Re: two fields in two tables as keyfields

anbu1984 — Tue, 12 May 2015 11:05:01 GMT

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

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

Re: two fields in two tables as keyfields

Tue, 12 May 2015 12:57:51 GMT

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

Re: two fields in two tables as keyfields

awhitfield — Tue, 12 May 2015 13:39:34 GMT

Nice one!

Andy