Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a table like this
T1:
Category | Date | Value |
A | 03/12/2019 | 968 |
B | 03/12/2019 | 478 |
C | 03/12/2019 | 259 |
D | 03/12/2019 | 478 |
A | 04/12/2019 | 300 |
B | 04/12/2019 | 658 |
C | 04/12/2019 | 478 |
D | 04/12/2019 | 214 |
A | 05/12/2019 | 200 |
D | 05/12/2019 | 800 |
I need result like this
T2:
Category | Date | Value |
A | 03/12/2019 | 968 |
B | 03/12/2019 | 478 |
C | 03/12/2019 | 259 |
D | 03/12/2019 | 478 |
A | 04/12/2019 | 300 |
B | 04/12/2019 | 658 |
C | 04/12/2019 | 478 |
D | 04/12/2019 | 214 |
A | 05/12/2019 | 200 |
B | 05/12/2019 | 658 |
C | 05/12/2019 | 478 |
D | 05/12/2019 | 800 |
The difference is, if you look at T1 there is missing value for Category B and C for 05/12/2019
If want to insert Category B and C for 05/12/2019 with the same value of B and C from 04/12/2019
I need 4 rows for each date from first date till today.
Please suggest how to achieve this from load script.
hi
this script is an example , that gives you what you looked for
T1temp:
LOAD * INLINE
[
Category,Date,Value
A,03/12/2019,968
B,03/12/2019,478
C,03/12/2019,259
D,03/12/2019,478
A,04/12/2019,300
B,04/12/2019,658
C,04/12/2019,478
D,04/12/2019,214
A,05/12/2019,200
D,05/12/2019,800
](delimiter is ',');
///////////creating a table with all dates and all category //////////////////////
T2temp:
load Distinct Date
Resident T1temp;
Join
load Distinct Category
Resident T1temp;
/////////////adding the actual data to the full table //////////////////////////////
left join (T2temp)
load *
Resident T1temp;
drop Table T1temp;
////////////////filling the missing values //////////////////
T2:
load *,
if(len(Value)<1,if(Previous(Category)=Category, peek('fullValue'),0),Value) as fullValue
Resident T2temp
Order by Category,Date;
drop table T2temp;
hi
this script is an example , that gives you what you looked for
T1temp:
LOAD * INLINE
[
Category,Date,Value
A,03/12/2019,968
B,03/12/2019,478
C,03/12/2019,259
D,03/12/2019,478
A,04/12/2019,300
B,04/12/2019,658
C,04/12/2019,478
D,04/12/2019,214
A,05/12/2019,200
D,05/12/2019,800
](delimiter is ',');
///////////creating a table with all dates and all category //////////////////////
T2temp:
load Distinct Date
Resident T1temp;
Join
load Distinct Category
Resident T1temp;
/////////////adding the actual data to the full table //////////////////////////////
left join (T2temp)
load *
Resident T1temp;
drop Table T1temp;
////////////////filling the missing values //////////////////
T2:
load *,
if(len(Value)<1,if(Previous(Category)=Category, peek('fullValue'),0),Value) as fullValue
Resident T2temp
Order by Category,Date;
drop table T2temp;
Thanks Buddy
This is what I needed