Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

JOIN / Concatenate 2 tables: Help

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

1 Solution

Accepted Solutions
chauhans85
Esteemed Contributor

JOIN / Concatenate 2 tables: Help

take resident of two tables

ualify two tables

un qualify field required to join

Join Them

Regards

Sunil

7 Replies
lironbaram
Honored Contributor II

JOIN / Concatenate 2 tables: Help

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

Not applicable

JOIN / Concatenate 2 tables: Help

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

chauhans85
Esteemed Contributor

JOIN / Concatenate 2 tables: Help

take resident of two tables and join them.

Regards

Sunil

Not applicable

JOIN / Concatenate 2 tables: Help

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

chauhans85
Esteemed Contributor

JOIN / Concatenate 2 tables: Help

take resident of two tables

ualify two tables

un qualify field required to join

Join Them

Regards

Sunil

Not applicable

JOIN / Concatenate 2 tables: Help

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;

chauhans85
Esteemed Contributor

JOIN / Concatenate 2 tables: 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]);

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

Community Browser