I need to generate a table like below. it is an incremental load and will be run weekly. i have uploaded the initial data and saved in QVD file. but i am strugling how i can load data from QVD file and append the new data to the old data from QVD in the table format below.
i have two issues:
- how to lable the columns
- when i am adding the new data to the old data, it is getting added to the rows not columns. i want new columns to get added to my straight table
Count_of_Status:
load CS_Grouped_Status AS COS_Grouped_Status,
CS_Date as COS_Date,
COUNT(CS_ID) as COS_Count
Resident Current_Status
Group by CS_Grouped_Status, CS_Date;
LoadQVD_AppsInGI:
LOAD COS_Grouped_Status as GI_Status,
COS_Date as GI_Date,
COS_Count AS GI_Count
from file.qvd (qvd);
// Trying append the new data with the old data available in QVD file
Incremental:
Load GI_Status as COS_Grouped_Status ,
GI_Date as COS_Date ,
GI_Count as COS_Count
Resident LoadQVD_AppsInGI;
LEFT JOIN
LOAD COS_Grouped_Status,
COS_Date,
COS_Count as [$(gear_date)]
Resident Count_of_Status;
Status | 14-May | 21-May | 28-May |
Total 'Live' Apps | 32 | 33 | 38 |
Total Decom/Withdrawn Apps | 4 | 3 | 2 |
Grand Total Apps | 36 | 36 | 40 |