topic Re: Issue with retrieve all values from Left table in QlikView

Issue with retrieve all values from Left table

ssquare — Fri, 05 Jun 2020 20:21:13 GMT

I need some help in implementing the concept of Left join/Left keep in Qlikview. Not sure if this is a correct approach for my problem, Suggest me if there is any other better approach :

I have a Budget table which contains Budget by Month for a FY & a Service table with Actual values. My issue, is i would need the budget values even though if there is no service for a specific day.

Budget Table

Code |Location| MonthYear| BudgetCost

001 A Jul2019 $100

002 B Jul2019 $500$

003 C Jul2019 $60

Service Table(Actuals)

Code |S_Location | Date | MonthYear| ActualCost

001 A 7/1/2019 Jul2019 $80

001 A 7/2/2019 Jul2019 $80

002 B 7/1/2019 Jul2019 $300

Expected output

Location | Cost | MonthYear | Day1 | Day 2| Day 3| Day 4

A Actual Jul2019 $80 $80 $0

A Budget Jul2019 $100 $100 $100

B Actual Jul2019 $300 $0 $0

B Budget Jul2019 $500 $500 $500

C Actual Jul2019 $0 $0 $0

C Budget Jul2019 $60 $60 $60

Following is the Script i'm using, I tried Left Join, Left Keep, Outer Join. But still I'm getting $0 for Budget for the values that don't have service. If there is a budget for a specific month, irrespective if there is a service or not i need the Budget values to be displayed.

QUALIFY *;
UNQUALIFY Code, MonthYear;

Budget:

LOAD Code,Location,MonthYear,BudgetCost

SQL SELECT *

FROM Budget (NOLOCK);

QUALIFY *;
UNQUALIFY Code, MonthYear;
LEFT KEEP(Budget)

Service:

LOAD Code,S_Location,MonthYear,ActualCost,Date

SQL SELECT *

FROM Service (NOLOCK);

Re: Issue with retrieve all values from Left table

Saravanan_Desingh — Sat, 06 Jun 2020 16:44:29 GMT

Are you looking something like this?

BudgetTable: LOAD * INLINE [ Code, Location, MonthYear, BudgetCost 1, A, Jul-19, 100 2, B, Jul-19, 500 3, C, Jul-19, 60 ]; ServiceTable: LOAD * INLINE [ Code, S_Location, Date, MonthYear, ActualCost 1, A, 7/1/2019, Jul-19, 80 1, A, 7/2/2019, Jul-19, 80 2, B, 7/1/2019, Jul-19, 300 ]; tab1: NoConcatenate LOAD Code, Location, MonthYear, BudgetCost As CostV, AutoNumber(RowNo(),Location& 'Budget') As Days, 'Budget' As Cost Resident BudgetTable; Concatenate(tab1) LOAD Code, S_Location As Location, Date, MonthYear, ActualCost As CostV, AutoNumber(RowNo(),S_Location) As Days, 'Actual' As Cost Resident ServiceTable; tab2: LOAD 0 AutoGenerate 1; Gen: Generic LOAD Location, Cost, MonthYear, 'Day'&Days, CostV Resident tab1; FOR i = 0 to NoOfTables() TableList: LOAD TableName($(i)) as Tablename AUTOGENERATE 1 WHERE WildMatch(TableName($(i)), 'Gen.*'); NEXT i FOR i = 1 to FieldValueCount('Tablename') LET vTable = FieldValue('Tablename', $(i)); LEFT JOIN (tab2) LOAD * RESIDENT $(vTable); DROP TABLE $(vTable); NEXT i Drop Table BudgetTable, ServiceTable, tab1, TableList; Drop Field [0];

Re: Issue with retrieve all values from Left table

Saravanan_Desingh — Sat, 06 Jun 2020 16:45:29 GMT

Re: Issue with retrieve all values from Left table

ssquare — Mon, 08 Jun 2020 18:55:05 GMT

That didn't work. I would need all the budget values for every location & day, Even there is no Actual values.

Re: Issue with retrieve all values from Left table

Saravanan_Desingh — Mon, 08 Jun 2020 20:06:18 GMT

How the Day1, Day2.. are calculated? Should I have to take the Date from the Actuals for Budget?

Re: Issue with retrieve all values from Left table

ssquare — Mon, 08 Jun 2020 21:55:11 GMT

Day1, Day2 gets from the Date column of Services table or by joining the Budget table, Service to the Mastercalendar or temp date table.

Re: Issue with retrieve all values from Left table

Saravanan_Desingh — Tue, 09 Jun 2020 01:33:55 GMT

Check this one. I have considered 5 Days.

BudgetTable: LOAD * INLINE [ Code, Location, MonthYear, BudgetCost 1, A, Jul-19, 100 2, B, Jul-19, 500 3, C, Jul-19, 60 ]; ServiceTable: LOAD * INLINE [ Code, S_Location, Date, MonthYear, ActualCost 1, A, 7/1/2019, Jul-19, 80 1, A, 7/2/2019, Jul-19, 80 2, B, 7/1/2019, Jul-19, 300 ]; Cal: LOAD Date(MinDate+IterNo()-1) As Date While IterNo() <= 5 ; LOAD Min(Date) As MinDate Resident ServiceTable; Left Join(BudgetTable) LOAD * Resident Cal; Left Join(ServiceTable) LOAD * Resident Cal; tab1: NoConcatenate LOAD Code, Location, MonthYear, BudgetCost As CostV, AutoNumber(RowNo(),Location& 'Budget') As Days, 'Budget' As Cost Resident BudgetTable; Concatenate(tab1) LOAD Code, S_Location As Location, Date, MonthYear, ActualCost As CostV, AutoNumber(RowNo(),S_Location) As Days, 'Actual' As Cost Resident ServiceTable; tab2: LOAD 0 AutoGenerate 1; Gen: Generic LOAD Location, Cost, MonthYear, 'Day'&Days, CostV Resident tab1; FOR i = 0 to NoOfTables() TableList: LOAD TableName($(i)) as Tablename AUTOGENERATE 1 WHERE WildMatch(TableName($(i)), 'Gen.*'); NEXT i FOR i = 1 to FieldValueCount('Tablename') LET vTable = FieldValue('Tablename', $(i)); LEFT JOIN (tab2) LOAD * RESIDENT $(vTable); DROP TABLE $(vTable); NEXT i Drop Table BudgetTable, ServiceTable, tab1, TableList, Cal; Drop Field [0];

Re: Issue with retrieve all values from Left table

Saravanan_Desingh — Tue, 09 Jun 2020 01:34:49 GMT

Re: Issue with retrieve all values from Left table

Brett_Bleess — Wed, 01 Jul 2020 13:37:20 GMT

If Saran's latest posts got you the information you needed, we would greatly appreciate you closing out the thread by using the Accept as Solution button on the post(s) that helped you get things working. This gives them credit for the assistance and lets other Community Members know what worked for the use case. If you did something different, please consider posting what you did and then use the button to mark that post as the solution, and if you require further help, please leave an update post as to what you still need.

Regards,
Brett