Loop for subfield-function and generate missing dates?

[kruel830]

I have a problem with my dataset. Data is for weekly item consumtion estimates, incoming pieces etc. The date for each item is start of each week (monday) and values are weekly total values. Daily values are available too, but those are stored in semicolon separeted list.

I need to have those daily values and store them in to new rows. Probably I need to use Subfield funtion and somekind of loop, but I don't know how to execute it. Please help.

Current situation:

Item	Date	Weekly_consumtion	Daily_consumption	Weekly_Incoming	Daily_Incoming
A	8.3.2021	50	"20;4;6;2;18;0;0"	60	"20;0;20;20;0;0;0"
B	8.3.2021	40	"8;4;13;5;10;0;0"	55	"10;10;10;10;15;0;0"
A	15.3.2021	45	"9;8;7;8;13;0;0"	50	"10;10;10;10;10;0;0"
B	15.3.2021	43	"12;14;0;4;13;0;0"	50	"20;10;10;0;10;0;0"

Desired output:

ItemCode	Date	Daily_consumption	Daily_Incoming
A	8.3.2021	20	20
A	9.3.2021	4	0
A	10.3.2021	6	20
A	11.3.2021	2	20
A	12.3.2021	18	0
A	13.3.2021	0	0
A	14.3.2021	0	0
A	15.3.2021	9	10
A	16.3.2021	8	10

etc...

[Saravanan_Desingh · Accepted solution]

Try this,

SET DateFormat='D.M.YYYY';

tab1:
LOAD *, Date(Date+IterNo()-1) As _Date,
	SubField(Daily_consumption,';',IterNo()) As _Daily_consumption,
	SubField(Daily_Incoming,';',IterNo()) As _Daily_Incoming
While IterNo()-1<=SubStringCount(Daily_consumption,';');
LOAD * INLINE [
    Item, Date, Weekly_consumtion, Daily_consumption, Weekly_Incoming, Daily_Incoming
    A, 8.3.2021, 50, "20;4;6;2;18;0;0", 60, "20;0;20;20;0;0;0"
    B, 8.3.2021, 40, "8;4;13;5;10;0;0", 55, "10;10;10;10;15;0;0"
    A, 15.3.2021, 45, "9;8;7;8;13;0;0", 50, "10;10;10;10;10;0;0"
    B, 15.3.2021, 43, "12;14;0;4;13;0;0", 50, "20;10;10;0;10;0;0"
];

[Saravanan_Desingh]

Output:

[kruel830]

Works perfectly, thank you!