Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ccb-it2013
Contributor
Contributor

How do a join with 2 dbf files?

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!

49 Replies
jerifortune
Creator III
Creator III

LEFT JOIN (CONMANPO_H)


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





vishsaggi
Champion III
Champion III

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

ccb-it2013
Contributor
Contributor
Author

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
Champion III
Champion III

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
Contributor
Contributor
Author

Hi Vishwarath,

No errors! Cool!!

Thanks for your help....

Much appreciated!

vishsaggi
Champion III
Champion III

Close the thread accordingly.

ccb-it2013
Contributor
Contributor
Author

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
Champion III
Champion III

yes you can alias using AS keyword.

sasiparupudi1
Master III
Master III

SQL

SELECT * FROM

ADOCONMANPO_h  ADOCONMANPO_h

LEFT JOIN

ADOCONMANPO_D ADOCONMANPO_D

ON ADOCONMANPO_h.PONO=ADOCONMANPO_D.TICKET