Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
I have table :
and need to get like this throw the script
I need to get one table because it will be store qvd for next working.
Help me please)
Regards,
Yelena
Hi @MilkyWay,
Here's one possible method:
//Upload your generic information (by creating a key) and with your initial date
TEMP:
Load
ID_D&ID_K&ID_C as KEY,
ID_D,ID_K,ID_C,Data_P,Sum_P
FROM your_data
where not isnull(Data_P);
//Join the rest of your missing information using your key, and you'll get a non-empty table
left join(TEMP)
Load
ID_D&ID_K&ID_C as KEY,
Data_F,Sum_F
FROM your_data
where not isnull(Data_F);
//To remove values where Sum_P must be 0, create a new table, sorted by key and date Data_F, if your key is identical to the previous one (Peek(KEY)=KEY) then 0, otherwise Sum_P since it's the first row
NoConcatenate
FINAL:
Load
KEY,ID_D,ID_K,ID_C,Data_P,
if(Peek(KEY)=KEY,0,Sum_P) as Sum_P,
Data_F,Sum_F
Resident TEMP
order by KEY,Data_F;
drop table TEMP;
Pierrick
Hi @MilkyWay,
Here's one possible method:
//Upload your generic information (by creating a key) and with your initial date
TEMP:
Load
ID_D&ID_K&ID_C as KEY,
ID_D,ID_K,ID_C,Data_P,Sum_P
FROM your_data
where not isnull(Data_P);
//Join the rest of your missing information using your key, and you'll get a non-empty table
left join(TEMP)
Load
ID_D&ID_K&ID_C as KEY,
Data_F,Sum_F
FROM your_data
where not isnull(Data_F);
//To remove values where Sum_P must be 0, create a new table, sorted by key and date Data_F, if your key is identical to the previous one (Peek(KEY)=KEY) then 0, otherwise Sum_P since it's the first row
NoConcatenate
FINAL:
Load
KEY,ID_D,ID_K,ID_C,Data_P,
if(Peek(KEY)=KEY,0,Sum_P) as Sum_P,
Data_F,Sum_F
Resident TEMP
order by KEY,Data_F;
drop table TEMP;
Pierrick
Hi Pierrick, Thanks a lot! 😊