Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
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;
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
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
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