What am I doing wrong in the below inner join? I am new to qlikview, please help.

[haneeshmarella]

I have two tables: PHPICK00 and PRTRAN00. I am trying for an inner join based on the columns PHPCTL.PHPICK00=PRPCTL.PRTRAN00. The QV is connected to a Warehouse Management Software via ODBC connection. It gives me the below error. Please help.

Data_Source:

SQL

Select

DISTINCT PHPKTN.PHPICK00,

PHSHCN.PHPICK00,

PHSVIA.PHPICK00,

PHTCT.PHPICK00,

PRUSER.PRTRAN00,

PRCASN.PRTRAN00,

PRTLOC.PRTRAN00,

PRMNOP.PRTRAN00,

PRFLOC.PRTRAN00,

Date(Date#(PRDCR.PRTRAN00,'YYYYMMDD'),'MM/DD/YYYY'),

Time(Time#(PRTCR.PRTRAN00,'hhmmss'),'hh:mm:ss'),

PRSTYL.PRTRAN00,

PRUNTS.PRTRAN00

FROM PHPICK00 INNER JOIN PRTRAN00

ON PHPCTL.PHPICK00=PRPCTL.PRTRAN00

WHERE PRTXTP.PRTRAN00='500'

AND PRTXCD.PRTRAN00='002'

AND PRWHSE.PRTRAN00='FG3'

AND PRDIV.PRTRAN00='08'

AND PRDCR.PRTRAN00>=?

AND PRMNOP.PRTRAN00='Pick/Pack'

AND PRCASN.PRTRAN00>''

AND wildmatch(PRFLOC.PRTRAN00,*    M3*)

ORDER BY PRUSER.PRTRAN00, Date(Date#(PRDCR.PRTRAN00,'YYYYMMDD'),'MM/DD/YYYY'), Time(Time#(PRTCR.PRTRAN00,'hhmmss'),'hh:mm:ss');

Also, the query pulls larger amount of data, how can I minimize the time to execute the above query?

Thank you in advance.

[vikramv]

I am thinking your JOIN condition is wrong it should be like TABLENAME.COLUMNNAME

In the select clause

SELECT

TABLENAME.COLUMNNAME

FROM PHPICK00 INNER JOIN PRTRAN00

ON PHPICK00.PHPCTL = PRTRAN00.PRPCTL

For reducing the performance of the query you should remove the ORDER BY and DISTINCT from your query.

You can implement the DISTINCT and ORDER BY in resident load if neccessary after your first load.

[wdchristensen]

I think it is because you are mixing up Qlik script function with SQL commands. Date# AND Time# are functions that don't exist in SQL.

[vishsaggi]

Try this? And you are using your aliases incorrectly, it should be Tablename.Fieldname not Fieldname.TableName. Corrected below.

Data_Source:

LOAD  *,

            Date(Date#(PRDCR,'YYYYMMDD'),'MM/DD/YYYY') AS PRDCRDt,

            Time(Time#(PRTCR,'hhmmss'),'hh:mm:ss')                AS PRTCRTime

WHERE wildmatch(PRFLOC,*    M3*);

SQL

Select

DISTINCT PHPICK00.PHPKTN,

PHPICK00.PHSHCN,

PHPICK00.PHSVIA,

PHPICK00.PHTCT,

PRTRAN00.PRUSER,

PRTRAN00.PRCASN,

PRTRAN00.PRTLOC,

PRTRAN00.PRMNOP,

PRTRAN00.PRFLOC,

PRTRAN00.PRSTYL,

PRTRAN00.PRUNTS

FROM PHPICK00 INNER JOIN PRTRAN00

ON PHPICK00.PHPCTL    = PRTRAN00.PRPCTL

WHERE PRTRAN00.PRTXTP ='500'

AND PRTRAN00.PRTXCD   ='002'

AND PRTRAN00.PRWHSE   ='FG3'

AND PRTRAN00.PRDIV    ='08'

AND PRTRAN00.PRDCR   >= '?'

AND PRTRAN00.PRMNOP   ='Pick/Pack'

AND PRTRAN00.PRCASN   >''

ORDER BY PRTRAN00.PRUSER, PRTRAN00.PRDCR, PRTRAN00.PRTCR;

[haneeshmarella]

Hi, I tweaked it as you have suggested and got the data. However, the last line highlighted in bold does not load when I tried, can you please help me filter out the source data based on the condition that the column PRFLOC should contain only '*    M3*'.

Data_Source:

SQL SELECT DISTINCT A.PHPKTN AS PICKTICKET#,

    A.PHSHCN AS SHIPTOCOUNTRY,

    A.PHSVIA AS PLANNEDSHIPVIA,

    A.PHTCT AS TOTALNOOFCARTONS,

    B.PRUSER AS PKMS_USERS,

    B.PRCASN AS CASE#,

    B.PRTLOC AS TO_LOCATION,

    B.PRMNOP AS MENU_OPTION_NAME,

    B.PRFLOC AS FROM_LOCATION,

    B.PRSTYL AS STYLE,

    B.PRUNTS AS UNITS#,

    B.PRDCR AS DATE_CREATED,

    B.PRTCR AS TIME_CREATED

FROM CAPM01.WM0272PRDD.PHPICK00 A, CAPM01.WM0272PRDD.PRTRAN00 B

WHERE A.PHPCTL=B.PRPCTL

AND B.PRTXTP='500'

AND B.PRTXCD='002'

AND B.PRWHSE='FG3'

AND B.PRDIV='08'

AND B.PRDCR>'20180607'

AND B.PRMNOP='Pick/Pack'

AND B.PRCASN IS NOT NULL --> Query worked till here

AND wildmatch("B.PRFLOC",'*    M3*'); --> This does not work and I need to filter it out to reduce the data to be loaded.

Thank you in advance.

[wdchristensen]

AND B.PRFLOC Like '*M3*'  // For Access

AND B.PRFLOC Like '%M3%'  // For SQL Server

[haneeshmarella]

Works great with SQL Server syntax. Thank you very much William.