topic Re: Fill table with datas while the first date less than the second date in App Development

Fill table with datas while the first date less than the second date

TakacsZsolt — Sat, 16 Nov 2024 19:55:06 GMT

Hi Experts,

I got stuck in development, please help me a little bit. 😊

I have a table, which contains rows of inter-warehouse transfer. I would like to fill the table with other days, when the products are in the truck.

The table:

Product	Quantity	VoucherDate	FulfillmentDate
1	10	2019-10-04	2019-10-06
2	20	2019-10-04	2019-10-09
3	5	2019-08-09	2019-08-14
4	8	2019-08-09	2019-08-12

What I want to see:

Product	Quantity	VoucherDate	FulfillmentDate
1	10	2019-10-04	2019-10-06
1	10	2019-10-05	2019-10-06
2	20	2019-10-04	2019-10-09
2	20	2019-10-05	2019-10-09
2	20	2019-10-06	2019-10-09
2	20	2019-10-07	2019-10-09
2	20	2019-10-08	2019-10-09
3	5	2019-08-09	2019-08-14
3	5	2019-08-10	2019-08-14
3	5	2019-08-11	2019-08-14
3	5	2019-08-12	2019-08-14
3	5	2019-08-13	2019-08-14
4	8	2019-08-09	2019-08-12
4	8	2019-08-10	2019-08-12
4	8	2019-08-11	2019-08-12

Thanks for reply!

Re: Fill table with datas while the first date less than the second date

StarinieriG — Fri, 11 Oct 2019 07:30:27 GMT

Hi,

try in this way

INPUT:
LOAD * Inline [
Product,Quantity,VoucherDate,FulfillmentDate
1,10,2019-10-04,2019-10-06
2,20,2019-10-04,2019-10-09
3,5,2019-08-09,2019-08-14
4,8,2019-08-09,2019-08-12
]
;

NoConcatenate
OUTPUT:
LOAD
' ' AS TEMP
AutoGenerate 0;

FOR i=0 to NoOfRows('INPUT')-1

LET Product = Peek('Product',$(i),'INPUT');
LET Quantity = Peek('Quantity','$(i)','INPUT');
LET VoucherDate = Peek('VoucherDate','$(i)','INPUT');
LET FulfillmentDate = Peek('FulfillmentDate','$(i)','INPUT');

Concatenate
LOAD
'$(Product)' as Product,
'$(Quantity)' as Quantity,
Date(('$(VoucherDate)'+IterNo()-1)) as VoucherDate,
Date('$(FulfillmentDate)') as FulfillmentDate
AutoGenerate 1 While ('$(VoucherDate)'+IterNo()-1) < '$(FulfillmentDate)'
;

DROP Table INPUT;

DROP Field TEMP;

Re: Fill table with datas while the first date less than the second date

TakacsZsolt — Fri, 11 Oct 2019 07:51:55 GMT

Dear StarinieriG,

Thanks for your replay, it works perfectly, but I search the solution without loop. Unfortunately the loop is very slow with big table.😕

Re: Fill table with datas while the first date less than the second date

anushree1 — Fri, 11 Oct 2019 08:33:40 GMT

Please check the attached

Re: Fill table with datas while the first date less than the second date

TakacsZsolt — Fri, 11 Oct 2019 09:00:33 GMT

Dear anushree1,

It's perfect! Thank You so much, this is what I was looking for.👍