If you don't care about keeping the dates with blank KPI values then when you load the first table simply add a where clause to leave the blank rows behind, then you will be able to concatenate the second table.
DataTable:
Load
Date,
KPI,
Value
From {whatever file or source for your first table}
Where Not IsNull(KPI);
It looks from your example like the values would be NULL. But you can adjust at times in the event you have blanks, or spaces etc as needed.
The above will work with concatenate for your second table, assuming you don't have duplicate dates in your second table. So now let's consider the duplicates situation. If there can/will be duplicates, and the second table is what you want to keep, then load them first. And then do a concatenate load for the first table with a clause that appends only the rows from the first table that do not already exist.
Where Not Exists(Date);
Here is some example code where I use INLINE to create the two dummy sources and I show how you use the exists clause in cases where you are aliasing fields. Notice that I'm saying if the value in Field1 that I'm loading from the first source doesn't already exist as a Date value.
Source1:
Load * Inline [
Field1, Field2, Field3
1/1/2022, 1, 399
2/1/2022, 1, 300
3/1/2022, 1, 100
4/1/2022,, 0
5/1/2022,, 5
];
Source2:
NoConcatenate Load * Inline [
Field1, Field2, Field3
3/1/2022,1,200
4/1/2022,1,500
5/1/2022,1,150
6/1/2022,1,100
];
MyDataTable:
Load
Field1 as Date,
Field2 as KPI,
Field3 as Value
Resident Source2;
Concatenate Load
Field1 as Date,
Field2 as KPI,
Field3 as Value
Resident Source1
Where Not Exists(Date, Field1);
Drop Table Source1;
Drop Table Source2;
Data modeling can be so much fun. I've been a Qlikkie now for 13 years and I still revel in the fact that in Application 1, I can use 1 technique and when the users for Application 2 provide crazy different requirements I can use a totally different technique.
