How do a join with 2 dbf files?

[ccb-it2013]

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!

[jerifortune]

LEFT JOIN (CONMANPO_H)

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

[vishsaggi]

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

[ccb-it2013]

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.

[vishsaggi]

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?

[ccb-it2013]

Hi Vishwarath,

No errors! Cool!!

Thanks for your help....

Much appreciated!

[vishsaggi]

Close the thread accordingly.

[ccb-it2013]

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!

[vishsaggi]

yes you can alias using AS keyword.

[sasiparupudi1]

SQL

SELECT * FROM

ADOCONMANPO_h  ADOCONMANPO_h

LEFT JOIN

ADOCONMANPO_D ADOCONMANPO_D

ON ADOCONMANPO_h.PONO=ADOCONMANPO_D.TICKET