JOIN / Concatenate 2 tables: Help

[#1]

Hi All,

I have the following script...

CrossTableDataNC:
CrossTable(WkEndDate, PayValue, 7)
LOAD ClientName,
TempNo,
TempName,
CategoryDesc,
Employer,
Year,
Invoice,
[40628],
[40635],
[40642],
[40649]
FROM
[Data\FPT\FPT April 2011.xlsx]
(ooxml, embedded labels, table is [Data - Normal Hours Cost]);


CrossTableDataNH:
CrossTable(WkEndDate, Hours, 7)
LOAD ClientName,
TempNo,
TempName,
CategoryDesc,
Employer,
Year,
Invoice,
[40628],
[40635],
[40642],
[40649]
FROM
[Data\FPT\FPT April 2011.xlsx]
(ooxml, embedded labels, table is [Data - Normal Hours Hours]);

Esssentially the data is identical except table one has a field PayValue and table 2 has a field Hours. I want to join these tables but I cant seem to figure out how. Please help, thanks Byron

[lironbaram]

hei byron

when you say i want to join the tables

what you mean

what are the fields indenticale in both tables

or the join should be by all 8 indetical fields

[#3]

Hi Liron

Thanks for your quick reply. ALL FIELDS are idendtical EXCEPT the ONE WHERE THE CROSSTABLE QUALIFIER IS MENTIONED i.e. PayValue and Hours. Each table has also exactly the same number of rows. I cant explain why I cannot sort this out with the data before I load it, but need to sort it out within QV script environment. The payvalue amount corresponds to the number of hours delivered

I want to JOIN the 2 tables together or essentially add the field HOURS to the first table in another column

[SunilChauhan]

take resident of two tables and join them.

Regards

Sunil

[#5]

To add, if I just load the tables it joins perfectly but creates an unnecessary syn key table. I want it to join but to not create this syn key

[SunilChauhan · Accepted solution]

take resident of two tables

ualify two tables

un qualify field required to join

Join Them

Regards

Sunil

[#7]

Thanks Sunil. Suprisingly enough i tried something similar and it didnt work, this time round it did. Would you mind changing my script below to incorporate what you mean by qualify and unqualify. Thanks very much to everyones help

CrossTableDataNC:
CrossTable(WkEndDate, PayValue, 7)
LOAD ClientName,
TempNo,
TempName,
CategoryDesc,
Employer,
Year,
Invoice,
[40628],
[40635],
[40642],
[40649]
FROM
[Data\FPT\FPT April 2011.xlsx]
(ooxml, embedded labels, table is [Data - Normal Hours Cost]);

CrossTableDataNCF:
LOAD *,
1 as Dummy
Resident CrossTableDataNC;
Drop Table CrossTableDataNC;

CrossTableDataNH:
CrossTable(WkEndDate, Hours, 7)
LOAD ClientName,
TempNo,
TempName,
CategoryDesc,
Employer,
Year,
Invoice,
[40628],
[40635],
[40642],
[40649]
FROM
[Data\FPT\FPT April 2011.xlsx]
(ooxml, embedded labels, table is [Data - Normal Hours Hours]);

JOIN (CrossTableDataNCF)
LOAD *,
1 as Dummy
Resident CrossTableDataNH;
Drop Table CrossTableDataNH;

[SunilChauhan]

CrossTableDataNC:
CrossTable(WkEndDate, PayValue, 7)
LOAD ClientName,
TempNo,
TempName,
CategoryDesc,
Employer,
Year,
Invoice,
[40628],
[40635],
[40642],
[40649]
FROM
[Data\FPT\FPT April 2011.xlsx]
(ooxml, embedded labels, table is [Data - Normal Hours Cost]);

CrossTableDataNH:
CrossTable(WkEndDate, Hours, 7)
LOAD ClientName,
TempNo,
TempName,
CategoryDesc,
Employer,
Year,
Invoice,
[40628],
[40635],
[40642],
[40649]
FROM
[Data\FPT\FPT April 2011.xlsx]
(ooxml, embedded labels, table is [Data - Normal Hours Hours]);

Qualify *;
Unqulify Fieldnamerequiredtojoin;
CrossTableDataNCF:
LOAD *,
1 as Dummy
Resident CrossTableDataNC;
Drop Table CrossTableDataNC;

JOIN (CrossTableDataNCF)
LOAD *,
1 as Dummy
Resident CrossTableDataNH;
Drop Table CrossTableDataNH;

Regards

Sunil Chauhan