Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
take resident of two tables
ualify two tables
un qualify field required to join
Join Them
Regards
Sunil
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
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
take resident of two tables and join them.
Regards
Sunil
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
take resident of two tables
ualify two tables
un qualify field required to join
Join Them
Regards
Sunil
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;
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