join several strings into one with key

[MilkyWay]

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

[Pierrick · Accepted solution]

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

[MilkyWay]

Hi Pierrick, Thanks a lot! 😊