topic JOIN / Concatenate 2 tables: Help in QlikView

JOIN / Concatenate 2 tables: Help

Thu, 28 Jul 2011 12:14:12 GMT

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

JOIN / Concatenate 2 tables: Help

lironbaram — Thu, 28 Jul 2011 12:19:31 GMT

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

JOIN / Concatenate 2 tables: Help

Thu, 28 Jul 2011 12:25:46 GMT

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

JOIN / Concatenate 2 tables: Help

SunilChauhan — Thu, 28 Jul 2011 12:30:44 GMT

take resident of two tables and join them.

Regards

Sunil

JOIN / Concatenate 2 tables: Help

Thu, 28 Jul 2011 12:30:45 GMT

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

JOIN / Concatenate 2 tables: Help

SunilChauhan — Thu, 28 Jul 2011 12:32:43 GMT

take resident of two tables

ualify two tables

un qualify field required to join

Join Them

Regards

Sunil

JOIN / Concatenate 2 tables: Help

Thu, 28 Jul 2011 12:36:25 GMT

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;

JOIN / Concatenate 2 tables: Help

SunilChauhan — Thu, 28 Jul 2011 12:45:48 GMT

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