Issue with retrieve all values from Left table

[ssquare]

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);

[Saravanan_Desingh]

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];

[Saravanan_Desingh]

[ssquare]

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

[Saravanan_Desingh]

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

[ssquare]

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

[Saravanan_Desingh]

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];

[Saravanan_Desingh]

[Brett_Bleess]

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