topic Assist with join code please in QlikView

Assist with join code please

QFanatic — Sat, 16 Nov 2024 18:51:06 GMT

Hello

I have a transaction table (that is currently missing one day's data for March - the 18th) and a Calendar table.

Code looks like this...

Dates_t:
select req_date
from
Date_Dim_Table where req_date between 20200301 and 20200331 (this returns 31 rows - correct)

Then I want to join to Trans table

Volumes:
LEFT join (Dates_t) Load *;

SELECT
log_date as req_date
,count(1) as hour_volume
FROM
Trans_Table
where log_date between 20200301 and 20200331
group by 1

I want the result to have Zero for hour_volume for the 18th - since there is no data, it shouldnt just be omitted from the Table.

I figured using a LEFTJoin would work, bit it doesnt .

Any assistance please. Thank you

MayilVahanan — Fri, 03 Apr 2020 04:07:04 GMT

Try like below

Dates_t:
select req_date
from
Date_Dim_Table where req_date between 20200301 and 20200331 (this returns 31 rows - correct)

Then I want to join to Trans table

Volumes:
LEFT join (Dates_t) Load *;

SELECT
log_date as req_date
,count(1) as hour_volume
FROM
Trans_Table
where log_date between 20200301 and 20200331
group by 1;

NoConcatenate

FinalTable:
Load req_date, Alt(hour_volume, 0) as B Resident Dates_t;

DROP Table Dates_t;

QFanatic — Fri, 03 Apr 2020 07:50:08 GMT

Thank you - forgot about ALT