Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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];
That didn't work. I would need all the budget values for every location & day, Even there is no Actual values.
How the Day1, Day2.. are calculated? Should I have to take the Date from the Actuals for Budget?
Day1, Day2 gets from the Date column of Services table or by joining the Budget table, Service to the Mastercalendar or temp date table.
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];
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