Joining multiple tables

[jessica_webb]

  Really hoping someone can help here as I am VERY stuck!

I am trying to keep all of my data (from several source) in one table. No idea if that's good practice, but I find it easier.

I load my first table:

Data:

LOAD

ID,

Name,

Age

From.....source1

Then next table:

Data:

LOAD

ID,

Name,

Age

From.....source2

All seems ok.

Then, I  match in more data:

tempData1:

CrossTable(Given, Target, 1)

LOAD

ID,

A,

B,

C

From...source3

JOIN (Data) LOAD*,

ApplyMap('Map_A',Given) as New

Resident tempData1;

Drop TABLE tempData1;

Drop FIELD Given;

Then the last bit of data:

tempData2:

CrossTable(Given, Target, 1)

LOAD

ID,

D,

E,

F

From...source4

JOIN (Data) LOAD*,

ApplyMap('Map_A',Given) as New

Resident tempData2;

Drop TABLE tempData2;

Drop FIELD Given;

And here it all goes wrong!

What I expect to see is (where 'Given' actually no longer exists - just showing it for clarity):

ID	Name	Age	New	Target	Given
INJ	Tom	42	Green	4	A
WMH	Sarah	31	Yellow	2	B
QMI	Ian	51	Blue	5	C
WXI	Laura	28	Yellow	8	D
OWM	Ben	97	Green	5	E
MQY	Paul	24	Blue	7	F

What I actually see is:

ID	Name	Age	New	Target	Given
INJ	Tom	42	Green	4	A
WMH	Sarah	31	Yellow	2	B
QMI	Ian	51	Blue	5	C
WXI	Laura	28
OWM	Ben	97
MQY	Paul	24
WXI	-	-	Yellow	8	D
OWM	-	-	Green	5	E
MQY	-	-	Blue	7	F

Can someone please tell me where I'm going wrong?? Why does it match the first temp table in ok, but not the second?

Thanks,
Jess

[sunny_talwar]

Concatenate before you join to data

Data:

LOAD

ID,

Name,

Age

From.....source1

Data:

LOAD

ID,

Name,

Age

From.....source2

tempData1:

CrossTable(Given, Target, 1)

LOAD

ID,

A,

B,

C

From...source3

Concatenate(tempData1)

CrossTable(Given, Target, 1)

LOAD

ID,

D,

E,

F

From...source4

JOIN (Data) LOAD*,

ApplyMap('Map_A',Given) as New

Resident tempData1;

Drop TABLE tempData1;

Drop FIELD Given;

[MarcoWedel]

Hi,

after the first join the Data table already has the Given and Target fields thus preventing a subsequent join on just ID like you expected.

hope this helps

regards

Marco

[jessica_webb]

Hi Sunny,

Thanks for the response.

I've tried this and am getting the error 'Illegal combination of prefixes' when the script gets to the 'Concatenate' part. From reading online, it seems because you can't concatenate a crosstable.

I've now changed it to

Data:

LOAD

ID,

Name,

Age

From.....source1

Data:

LOAD

ID,

Name,

Age

From.....source2

tempData1:

CrossTable(Given, Target, 1)

LOAD

ID,

A,

B,

C

From...source3

tempData2:

CrossTable(Given, Target, 1)

LOAD

ID,

D,

E,

F

From...source4

Concatenate (tempData1)

LOAD*Resident tempData2;

DROP TABLE tempData2;

JOIN (Data) LOAD*,

ApplyMap('Map_A',Given) as New

Resident tempData1;

Drop TABLE tempData1;

Drop FIELD Given;

But get a 'Table not found' error when it gets to Concatenate..

Any ideas?

Thanks,

Jess

[sunny_talwar · Accepted solution]

May be we don't need that part, try this

Data:

LOAD

ID,

Name,

Age

From.....source1

Data:

LOAD

ID,

Name,

Age

From.....source2

tempData1:

CrossTable(Given, Target, 1)

LOAD

ID,

A,

B,

C

From...source3

tempData2:

CrossTable(Given, Target, 1)

LOAD

ID,

D,

E,

F

From...source4

Concatenate (tempData1)

LOAD*Resident tempData2;

DROP TABLE tempData2;

JOIN (Data) LOAD*,

ApplyMap('Map_A',Given) as New

Resident tempData1;

Drop TABLE tempData1;

Drop FIELD Given;

[jessica_webb]

Hi Sunny,

I'd tried a fix of:

NoConcatenate

LOAD*Resident tempData1;

Concatenate(tempData1)

LOAD*Resident tempData2;

DROP TABLE tempData2;

Which worked. But removing it all together like you've suggested works too...

I'm confused. How does QV know to apply map to tempData2??

[sunny_talwar]

Since the two crosstables have the exact same names, it autoconcatenates the two. So when you put noconcatenate and then concatenate you are basically adding extra overhead here.

[jessica_webb]

Of course! The field names are becoming values with the cross table function, so that makes sense!

Thanks so much for your help!