topic Re: How do a join with 2 dbf files? in QlikView

How do a join with 2 dbf files?

ccb-it2013 — Wed, 25 Nov 2020 16:16:04 GMT

Hi there,

For the life of me I can't join 2 dbf files so that I can get the necessary data I need from the second database.

In my script I have this:

SELECT * FROM ADO CONMANPO_H

The name of the second dbf I need to get data from is called say CONMANPO_D . They field they have in common is called PONO.

I tried this:

LEFT JOIN CONMANPO_H on PONO as CONMANPO_D

However I get a script error when I try running the script.

What am I doing wrong?? I am sure it is something really simple.

Thanks for your help!

Re: How do a join with 2 dbf files?

jerifortune — Wed, 11 Apr 2018 18:25:30 GMT

LEFT JOIN (CONMANPO_H)

Should work if you have a common key (PONO) in both tables. Qlik uses associative model.

Re: How do a join with 2 dbf files?

vishsaggi — Wed, 11 Apr 2018 18:27:59 GMT

Can you share your script what you running where you getting this error?

Re: How do a join with 2 dbf files?

ccb-it2013 — Wed, 11 Apr 2018 18:36:51 GMT

Hi there,

See script below. Same issue as I mentioned in my post. I need to get information from ASCONMANPO_D. Common reference is TICKET in this case.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

OLEDB CONNECT32 TO [Provider=VFPOLEDB.1;Data Source=k:\QVDATA\;Collating Sequence=general;];

SELECT * FROM ADOCONMANPO_h

LEFT JOIN(ADOCONMANPO_D) ON TICKET

Thanks for your help. Feel silly to have to ask for something so simple.

Re: How do a join with 2 dbf files?

vishsaggi — Wed, 11 Apr 2018 18:42:05 GMT

Try this:

LOAD *;

SQL

SELECT adoh.Field1, adoh.Field2, adoh.TICKET, adod.TICKET, adod.fieldname

FROM

ADOCONMANPO_h AS adoh

LEFT JOIN

ADOCONMANPO_D AS adod

ON adoh.TICKET = adod.TICKET

When you join the tables try to use Table alias to refer to the fields from their respective tables. Add the fields you want to display in your SELECT list and execute.

Let me know if you get any error?

Re: How do a join with 2 dbf files?

ccb-it2013 — Wed, 11 Apr 2018 19:19:37 GMT

Hi Vishwarath,

No errors! Cool!!

Thanks for your help....

Much appreciated!

Re: How do a join with 2 dbf files?

vishsaggi — Wed, 11 Apr 2018 19:41:20 GMT

Close the thread accordingly.

Re: How do a join with 2 dbf files?

ccb-it2013 — Fri, 13 Apr 2018 16:28:02 GMT

Hi Vishwarath,

What do I do if the field in the database does not have the same name?

i.e.: DBF 1 the field is called PONO

DBF 2 the field is called TICKET

Is there a manner to make like an alias for the field so I can use it as a reference to find the data I need?

THanks!

Re: How do a join with 2 dbf files?

vishsaggi — Fri, 13 Apr 2018 17:27:46 GMT

yes you can alias using AS keyword.

Re: How do a join with 2 dbf files?

sasiparupudi1 — Sun, 15 Apr 2018 19:42:59 GMT

SQL

SELECT * FROM

ADOCONMANPO_h ADOCONMANPO_h

LEFT JOIN

ADOCONMANPO_D ADOCONMANPO_D

ON ADOCONMANPO_h.PONO=ADOCONMANPO_D.TICKET

Re: How do a join with 2 dbf files?

ccb-it2013 — Thu, 19 Apr 2018 18:07:47 GMT

Hi Sasidhar,

For some reason what you suggested does not work:

The database files are Visual FoxPro 9.x.

Is there something I am not understanding?

Thanks!

Re: How do a join with 2 dbf files?

vishsaggi — Thu, 19 Apr 2018 18:12:31 GMT

Are you still having issues joining these tables? Can you paste your actual script where you getting this error?

Re: How do a join with 2 dbf files?

ccb-it2013 — Thu, 19 Apr 2018 19:09:54 GMT

Hi Vishwarath,

This is the script I have:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

OLEDB CONNECT32 TO [Provider=VFPOLEDB.1;Data Source=G:\AIMS\QVDATA\CMP4\;Collating Sequence=general;];

SELECT * FROM ADOCONMANPO_h

SELECT * FROM ADOCONMANPO_h ADOPOLINS

LEFT JOIN

ADOCONMANPO_H ADOPOLINS

ON ADOCONMANPO_H.PONO=ADOPOLINS.PONO

This is error I get:

This table 1:

This is the table I need join to:

Ticket in table 1 contains the same reference in table 2 under field PONO

What I need is to be able access the fields in table 2 for the report.

What am I doing wrong? I understand to join two tables, you need a common reference or key.

Please advise.

Thanks!

Re: How do a join with 2 dbf files?

vishsaggi — Thu, 19 Apr 2018 19:31:34 GMT

NO this is not how you do a join. Try below and you have to mention what fields you want to pull from both the tables. If you have field which are similar in both the tables you need to specify from which table you want that common field to be displayed.

And are you trying to do a self join here? If yes you have to mention the fields appropriately. Can you provide what fields you want to display in your final report. Your script says you are joining the same table.

Re: How do a join with 2 dbf files?

ccb-it2013 — Thu, 19 Apr 2018 20:03:37 GMT

Hi Vishwarath,

Thank you for your patience and guidance. Much appreciated.

From the first table I need:

Ticket, Style, Vendor, Mfcode, duedate

From the second table :

Account, Color, desc, linetot (quantity of color), Shipno

Re: How do a join with 2 dbf files?

vishsaggi — Thu, 19 Apr 2018 20:32:07 GMT

Ok. one last thing what is the table name for First table and the table name for Second table?

Re: How do a join with 2 dbf files?

ccb-it2013 — Thu, 19 Apr 2018 20:37:10 GMT

The first table name is ADOCONMANPO_h

The second table name is ADOPOLINS

Many thanks!

Re: How do a join with 2 dbf files?

vishsaggi — Thu, 19 Apr 2018 20:55:11 GMT

Try this! If I did not enter the table names try to change accordingly.

OLEDB CONNECT32 TO [Provider=VFPOLEDB.1;Data Source=G:\AIMS\QVDATA\CMP4\;Collating Sequence=general;];
SQL
SELECT adoh.Ticket,
adoh.Style,
adoh.Vendor,
adoh.Mfcode,
adoh.duedate,
adoplins.Account,
adoplins.Color,
adoplins.desc,
adoplins.linetot
adoplins.shipno
FROM ADOCONMANPO_h adoh
LEFT JOIN
ADOPOLINS adoplins
ON adoh.Ticket = adoplins.PONO;

Re: How do a join with 2 dbf files?

ccb-it2013 — Fri, 20 Apr 2018 16:55:37 GMT

HI VIshwarath,

The names are good, but I am still getting the same error...

Re: How do a join with 2 dbf files?

sasiparupudi1 — Fri, 20 Apr 2018 17:11:47 GMT

May be there is no field called pono?

In your previous replies you said code ran well with the join

ON adoh.TICKET = adod.TICKET ?

May be change

ON adoh.Ticket = adoplins.PONO to above stmt