topic Re: Major Frustration and Problem With Joins in QlikView

Major Frustration and Problem With Joins

Thu, 09 Feb 2017 22:20:19 GMT

I am not sure why my joins won't work. Here is what I have. The error shows as "Table not found PFTC". Not sure why this is the case when I have it defined in a previous tab, as shown below. Thanks for the help. I am new to joins in qlikview:

PFTC:

LOAD ACTIVITY_ID,

CD_SERVICE_RESOURCE,

CHARGE_ITEM_ID,

CLIENT_BILL_IND,

CLIENT_ORG_ID,

PFT_CHARGE_ID,

PFT_ENCNTR_ID;

SQL SELECT *

FROM nhms2.Z_STAGE_PFT_CHARGE

where ACTIVE_FLAG =1 and ACTIVE_IND=1

and charge_item_id in (12669952076,12904771391,12905729988,12806709891,12671441968);

LEFT JOIN ([PFTENC]) load

PFT_ENCNTR_ID

Resident PFTC;

CHG:

LOAD

ENCNTR_ID,

CHARGE_ITEM_ID,

ABN_STATUS_DESC,

ACTIVITY_DT_TM,

num(Year(ACTIVITY_DT_TM),'###0') as Activity_Year,

num(Month(ACTIVITY_DT_TM),'00') as Activity_Month,

num(Day(ACTIVITY_DT_TM),'00') as Activity_Day,

If(IsNull(ACTIVITY_DT_TM)=0,'Q' & Ceil(Month(ACTIVITY_DT_TM)/3)) as Activity_Quarter,

num(DaynumberofYear(ACTIVITY_DT_TM),'00') as Activity_DayofYear,

Floor(ACTIVITY_DT_TM) as ACTIVITY_DT,

num(Month(ACTIVITY_DT_TM),'00') * 100 + num(Day(ACTIVITY_DT_TM),'00') as Activity_MonthDay,

ACTIVITY_TYPE_DESC,

ATTENDING_PHYS_ID,

BILL_ITEM_ID,

CD_ACTIVITY_TYPE,

CD_CHARGE_TYPE,

CD_MED_SERVICE,

CD_TIER_GROUP,

CHARGE_DESCRIPTION,

CHARGE_EVENT_ACT_ID,

CHARGE_EVENT_ID,

CHARGE_TYPE_DESC,

COMBINE_IND,

DEF_BILL_ITEM_ID,

DEPARTMENT_DESC,

FIN_NBR,

GROSS_PRICE,

INTERFACE_FILE_ID,

ITEM_PRICE,

ITEM_QUANTITY,

LEVEL5_DESC,

MANUAL_IND,

MED_NBR,

MED_SERVICE_DESC,

OFFSET_CHARGE_ITEM_ID,

ORD_PHYS_ID,

ORDER_ID,

ORGANIZATION_ID,

PAYOR_TYPE_DESC,

PERF_LOC_DESC,

PERF_PHYS_ID,

POSTED_DT_TM,

PRICE_SCHED_ID,

PROCESS_FLG,

REASON_COMMENT,

REF_PHYS_ID,

SECTION_DESC,

SERVER_PROCESS_FLAG,

Floor(Date(SERVICE_DT_TM)) as SERVICE_DT,

num(Year(SERVICE_DT_TM),'###0') as Service_Year,

num(Month(SERVICE_DT_TM),'00') as Service_Month,

num(Day(SERVICE_DT_TM),'00') as Service_Day,

If(IsNull(SERVICE_DT_TM)=0,'Q' & Ceil(Month(SERVICE_DT_TM)/3)) as Service_Quarter,

num(DaynumberofYear(SERVICE_DT_TM),'00') as Service_DayofYear,

num(Month(SERVICE_DT_TM),'00') * 100 + num(Day(SERVICE_DT_TM),'00') as Service_MonthDay,

SUBSECTION_DESC,

SUSPENSE_RSN_DESC,

VERIFY_PHYS_ID;

SQL SELECT *

FROM dba.v_charge

where order_id in (3092398827,3092656755,3137437089,3137928261,3165049041,3164842039);

LEFT JOIN ([PFTC]) load

CHARGE_ITEM_ID

Resident TEST;

Re: Major Frustration and Problem With Joins

Clever_Anjos — Thu, 09 Feb 2017 23:40:28 GMT

Whats the purpose of this?

LEFT JOIN ([PFTENC]) load

PFT_ENCNTR_ID

Resident PFTC;

Re: Major Frustration and Problem With Joins

jayanttibhe — Thu, 09 Feb 2017 23:51:14 GMT

In addition to Clever's comment - Whats this - Resident TEST ?

I am not able to locate TEST table.

Re: Major Frustration and Problem With Joins

sunny_talwar — Fri, 10 Feb 2017 00:05:28 GMT

Although I agree with both the comments above me, but in relation to the error you can try this

PFTC:

NoConcatenate

LOAD ACTIVITY_ID,

CD_SERVICE_RESOURCE,

CHARGE_ITEM_ID,

CLIENT_BILL_IND,

CLIENT_ORG_ID,

PFT_CHARGE_ID,

PFT_ENCNTR_ID;

SQL SELECT *

FROM nhms2.Z_STAGE_PFT_CHARGE

where ACTIVE_FLAG =1 and ACTIVE_IND=1

and charge_item_id in (12669952076,12904771391,12905729988,12806709891,12671441968);

NoConcatenate might be needed if you have another table which contains the same field names. Without the use of NoConcatenate, the script will auto concatenate this to the previous table and won't create a new table with the name PFTC. On the other hand, if you add NoConcatenate, script will force a creation of the table although it has the same field as another table before it.

Re: Major Frustration and Problem With Joins

sdmech81 — Fri, 10 Feb 2017 06:25:10 GMT

HI,

Can u attach complete script or qvw file if small bcs not clear frm above script and not able to locate tables like PFTENC and TEST.

Or try frm you end like aftr each step put exit script and check wthr tables r getting loaded properly or not.

Sachin

Re: Major Frustration and Problem With Joins

Fri, 10 Feb 2017 14:50:13 GMT

Clever,

Like I said, I am new to joins and don't really know what I am doing. I am really good at SQL, but I can't really wrap my head around how Qlikview does joins. I am trying to join the pftc table to the pftenc table, which seems to work. I am then trying to join the CHG table to the pftc and enc table, and I am not having any luck.

The above is not all of my qlikview code. I can post all of it in the next post I make so it is easier to read in one pane.

This is a sample of the SQL that works and I am trying to convert it to Qlikview:

select *

from ( select * from nhms2.z_stage_encounter where ENCNTR_ID in (76551089,76559885,76560999) and ACTIVE_FLAG=1 and active_ind=1 ) e

inner join nhms2.z_stage_pft_encntr pe on e.encntr_id = pe.encntr_id and pe.ACTIVE_FLAG=1 and pe.active_ind=1

inner join nhms2.z_stage_pft_charge pc on pe.pft_encntr_id = pc.pft_encntr_id and pc.ACTIVE_FLAG=1 and pc.active_ind=1

inner join nhms2.z_stage_charge c on e.ENCNTR_ID=c.ENCNTR_ID and pc.charge_item_id = c.charge_item_id and c.ACTIVE_FLAG=1 and c.active_ind=1

Re: Major Frustration and Problem With Joins

Fri, 10 Feb 2017 15:08:01 GMT

Here is the entire body of code. I didn't know what I was doing when I typed "resident test":

ENC:

LOAD ENCNTR_ID;

SQL SELECT *

FROM nhms2.Z_STAGE_ENCOUNTER

where ACTIVE_FLAG =1 and ACTIVE_IND=1

and ENCNTR_ID in (76551089,76559885,76560999);

PFTENC:

LOAD ENCNTR_ID,

PFT_ENCNTR_ID;

SQL SELECT *

FROM nhms2.z_stage_pft_encntr

where ACTIVE_FLAG =1 and ACTIVE_IND=1

and pft_encntr_id in (432750161,432830923,432750161);

INNER JOIN ([ENC]) load

ENCNTR_ID

Resident PFTENC;

PFTC:

LOAD ACTIVITY_ID,

CD_SERVICE_RESOURCE,

CHARGE_ITEM_ID,

CLIENT_BILL_IND,

CLIENT_ORG_ID,

PFT_CHARGE_ID,

PFT_ENCNTR_ID;

SQL SELECT *

FROM nhms2.Z_STAGE_PFT_CHARGE

where ACTIVE_FLAG =1 and ACTIVE_IND=1

and charge_item_id in (12669952076,12904771391,12905729988,12806709891,12671441968);

LEFT JOIN ([PFTENC]) load

PFT_ENCNTR_ID

Resident PFTC;

CHG:

LOAD

ENCNTR_ID,

CHARGE_ITEM_ID,

ABN_STATUS_DESC,

ACTIVITY_DT_TM,

num(Year(ACTIVITY_DT_TM),'###0') as Activity_Year,

num(Month(ACTIVITY_DT_TM),'00') as Activity_Month,

num(Day(ACTIVITY_DT_TM),'00') as Activity_Day,

If(IsNull(ACTIVITY_DT_TM)=0,'Q' & Ceil(Month(ACTIVITY_DT_TM)/3)) as Activity_Quarter,

num(DaynumberofYear(ACTIVITY_DT_TM),'00') as Activity_DayofYear,

Floor(ACTIVITY_DT_TM) as ACTIVITY_DT,

num(Month(ACTIVITY_DT_TM),'00') * 100 + num(Day(ACTIVITY_DT_TM),'00') as Activity_MonthDay,

ACTIVITY_TYPE_DESC,

ATTENDING_PHYS_ID,

BILL_ITEM_ID,

CD_ACTIVITY_TYPE,

CD_CHARGE_TYPE,

CD_MED_SERVICE,

CD_TIER_GROUP,

FIN_NBR,

GROSS_PRICE,

INTERFACE_FILE_ID,

ITEM_PRICE,

ITEM_QUANTITY,

LEVEL5_DESC,

MANUAL_IND,

MED_NBR,

MED_SERVICE_DESC,

OFFSET_CHARGE_ITEM_ID,

SQL SELECT *

FROM dba.v_charge

where order_id in (3092398827,3092656755,3137437089,3137928261,3165049041,3164842039);

LEFT JOIN ([PFTC]) load

CHARGE_ITEM_ID

Resident CHG;

LEFT JOIN ([ENC]) load

ENCNTR_ID

Resident CHG;

Re: Major Frustration and Problem With Joins

Fri, 10 Feb 2017 15:18:18 GMT

Hi Sachin,

Entire script is attached to the response to Clever.

Thanks,

Joel

Re: Major Frustration and Problem With Joins

Fri, 10 Feb 2017 15:36:54 GMT

One small update. I made a change to the code to get rid of the Resident Test and change it to Resident CHG and I also tried to do another join to mimic my SQL above. Weird thing is that this should join the table dba.v_charge (CHG) to the two tables Encounter ENC and to nhms2.Z_STAGE_PFT_CHARGE (PFTC):

LEFT JOIN ([PFTC]) load

CHARGE_ITEM_ID

Resident CHG;

LEFT JOIN ([ENC]) load

ENCNTR_ID

Resident CHG;

I now get a Cartesian product and a warning that says:

"One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. QlikView will cut the loop(s) by setting one or more tables as loosely coupled. Settings for loosely coupled tables can be modified after script execution in the tables page of the document properties dialog."

However, if you look at my SQL above, you only need to link the CHG table to the encounter table and the pft_charge table.

Thanks,

Joel

Re: Major Frustration and Problem With Joins

Clever_Anjos — Fri, 10 Feb 2017 16:21:51 GMT

If you´re familiar with SQL and your tables reside on same database I don´t see any problem you make your joins using regular SQL instead of translating it into Qlik

You can choose what it is better for your

Re: Major Frustration and Problem With Joins

Fri, 10 Feb 2017 16:31:51 GMT

Because I would like to do this one in Qlik as a learning experience. At this point, I have a Cartesian product and just need to fix that. This is being caused here:

LEFT JOIN ([PFTC]) load

CHARGE_ITEM_ID

Resident CHG;

LEFT JOIN ([ENC]) load

ENCNTR_ID

Resident CHG;

The SQL equivalent for that part is here:

inner join nhms2.z_stage_pft_charge PFTC on pe.pft_encntr_id = PFTC.pft_encntr_id

inner join nhms2.z_stage_charge CHG on ENC.ENCNTR_ID=CHG .ENCNTR_ID and PFTC.charge_item_id = CHG .charge_item_id

When I run it in SQL, I get the right number of rows. When I run the qlikview code, I get repeating rows. My join isn't working in Qlik. Anyone know why?

Thanks,

Joel

Re: Major Frustration and Problem With Joins

Clever_Anjos — Fri, 10 Feb 2017 16:55:08 GMT

Maybe this can solve your need (hard to test without your data)

ENC:
LOAD ENCNTR_ID;
SQL SELECT ENCNTR_ID
FROM nhms2.Z_STAGE_ENCOUNTER
where ACTIVE_FLAG =1 and ACTIVE_IND=1 and ENCNTR_ID in (76551089,76559885,76560999);

inner join (ENC)
LOAD
  ENCNTR_ID,
  PFT_ENCNTR_ID;
SQL SELECT *
FROM nhms2.z_stage_pft_encntr
where ACTIVE_FLAG =1 and ACTIVE_IND=1
and pft_encntr_id in (432750161,432830923,432750161);

//INNER JOIN ([ENC]) load
//  ENCNTR_ID
//Resident PFTENC;

LEFT JOIN(ENC)
LOAD ACTIVITY_ID,
    CD_SERVICE_RESOURCE,
    CHARGE_ITEM_ID,
    CLIENT_BILL_IND,
    CLIENT_ORG_ID,
    PFT_CHARGE_ID,
    PFT_ENCNTR_ID;
SQL SELECT ACTIVITY_ID,
    CD_SERVICE_RESOURCE,
    CHARGE_ITEM_ID,
    CLIENT_BILL_IND,
    CLIENT_ORG_ID,
    PFT_CHARGE_ID,
    PFT_ENCNTR_ID
FROM nhms2.Z_STAGE_PFT_CHARGE
where ACTIVE_FLAG =1 and ACTIVE_IND=1
and charge_item_id in (12669952076,12904771391,12905729988,12806709891,12671441968);

//LEFT JOIN ([PFTENC]) load
//    PFT_ENCNTR_ID
//Resident PFTC;

LEFT JOIN(ENC)
LOAD
    ENCNTR_ID,
    CHARGE_ITEM_ID,
    ABN_STATUS_DESC,
    ACTIVITY_DT_TM,
    num(Year(ACTIVITY_DT_TM),'###0') as Activity_Year,
    num(Month(ACTIVITY_DT_TM),'00') as Activity_Month,
    num(Day(ACTIVITY_DT_TM),'00') as Activity_Day,
    If(IsNull(ACTIVITY_DT_TM)=0,'Q' & Ceil(Month(ACTIVITY_DT_TM)/3)) as Activity_Quarter,
    num(DaynumberofYear(ACTIVITY_DT_TM),'00') as Activity_DayofYear,
    Floor(ACTIVITY_DT_TM) as ACTIVITY_DT,
    num(Month(ACTIVITY_DT_TM),'00') * 100 + num(Day(ACTIVITY_DT_TM),'00') as Activity_MonthDay,
    ACTIVITY_TYPE_DESC,
    ATTENDING_PHYS_ID,
    BILL_ITEM_ID,
    CD_ACTIVITY_TYPE,
    CD_CHARGE_TYPE,
    CD_MED_SERVICE,
    CD_TIER_GROUP,
    FIN_NBR,
    GROSS_PRICE,
    INTERFACE_FILE_ID,
    ITEM_PRICE,
    ITEM_QUANTITY,
    LEVEL5_DESC,
    MANUAL_IND,
    MED_NBR,
    MED_SERVICE_DESC,
    OFFSET_CHARGE_ITEM_ID,
  SQL SELECT *
FROM dba.v_charge
where order_id in (3092398827,3092656755,3137437089,3137928261,3165049041,3164842039);

//LEFT JOIN ([PFTC]) load
//      CHARGE_ITEM_ID
//Resident CHG;
//
//LEFT JOIN ([ENC]) load
//      ENCNTR_ID
//Resident CHG;

Re: Major Frustration and Problem With Joins

Peter_Cammaert — Fri, 10 Feb 2017 17:18:39 GMT

FYI QlikView JOINS and SQL JOINS operate in much the same way but - as you may have guessed already - you write them differently.

While a SQL join is selfcontained (everything - including complex JOINS - in a single statement), a QlikView JOIN must happen in discrete steps because of the fact that in every LOAD statement there is only one output table (resident in memory) and only one data source. Compare this to SQL where you also have one result set but each SELECT statement - by way of embedded JOIN or UNION glue logic - can load form different sources.

Also, in QlikView you cannot nest LOAD statements, which is a major feature in SQL.

On the positive side: JOIN prefixes are the same as in SQL (INNER, OUTER, LEFT, RIGHT) and they also operate in the same way.

And one tip: QlikView JOINs become way easier to write if you keep in mind that the SQL Join ... on ... clause is handled in QlikView by

giving fields whose content should match the same name. This is a common table linking technique in QlikView and is also used when throwing two tables together. It happens implicitly.
searches for explicit field content is handled by the WHERE clause.

Of course there are a huge amount of possible variations that make the QlikView way flexible, although it may look very rigid at first. It's just written in a different way.

Best,

Peter

Re: Major Frustration and Problem With Joins

Fri, 10 Feb 2017 22:24:11 GMT

Clever,

This helped a LOT. Thank you for getting me on my Qlikview feet.

I also want to thank the rest of you for chiming in as well. Your advice is appreciated.

Thanks,

Joel