topic Re: Repeat data for next dates in Visualization and Usability

Repeat data for next dates

nqlik123 — Wed, 07 Dec 2022 20:35:10 GMT

Hi All, I have data as below..

Date	ID	Amount
31-Oct-22	A1234	100
30-Nov-22	A4568	400

I want my data previous month end data to be repeated for next dates.. till next date. Next date data ie my November data should repeat till date ie today..
Expected output

Date	ID	Amount
31-Oct-22	A1234	100
01-Nov-22	A1234	100
02-Nov-22	A1234	100
03-Nov-22	A1234	100
04-Nov-22	A1234	100
05-Nov-22	A1234	100
06-Nov-22	A1234	100
07-Nov-22	A1234	100
08-Nov-22	A1234	100
09-Nov-22	A1234	100
10-Nov-22	A1234	100
11-Nov-22	A1234	100
12-Nov-22	A1234	100
13-Nov-22	A1234	100
14-Nov-22	A1234	100
15-Nov-22	A1234	100
16-Nov-22	A1234	100
17-Nov-22	A1234	100
18-Nov-22	A1234	100
19-Nov-22	A1234	100
20-Nov-22	A1234	100
21-Nov-22	A1234	100
22-Nov-22	A1234	100
23-Nov-22	A1234	100
24-Nov-22	A1234	100
25-Nov-22	A1234	100
26-Nov-22	A1234	100
27-Nov-22	A1234	100
28-Nov-22	A1234	100
29-Nov-22	A1234	100
30-Nov-22	A4568	400
01-Dec-22	A4568	400
02-Dec-22	A4568	400
03-Dec-22	A4568	400
04-Dec-22	A4568	400
05-Dec-22	A4568	400
06-Dec-22	A4568	400
07-Dec-22	A4568	400

Re: Repeat data for next dates

RudyKostka — Fri, 09 Dec 2022 14:53:46 GMT

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

TMP_Input:
LOAD Floor(Date#(Date, 'DD-MMM-YY')) as %Date, * INLINE [
Date, ID, Amount
31-Oct-22, A1234, 100
30-Nov-22, A4568, 400
];

TMP_Table:
LOAD
Tmp_MinDate + IterNo() as %Date
While Tmp_MinDate + IterNo() <= Tmp_MaxDate;
LOAD
Min (FieldValue ('%Date', RecNo ())) - 1 as Tmp_MinDate,
// Max (FieldValue ('%Date', RecNo ())) as Tmp_MaxDate
Floor(Today()) as Tmp_MaxDate
AutoGenerate FieldValueCount ('%Date');

LEFT JOIN (TMP_Table) LOAD * Resident TMP_Input;
DROP Table TMP_Input;

FINAL_Table:
Noconcatenate LOAD
%Date,
Date(%Date) as Date, 
IF(IsNull(ID), Peek(ID),ID) as ID, 
IF(IsNull(Amount), Peek(Amount),Amount) as Amount
Resident TMP_Table
Order by %Date;

DROP Table TMP_Table;

Re: Repeat data for next dates

vinieme12 — Thu, 08 Dec 2022 07:19:57 GMT

An alternative way

temp: load date(date#(Date,'DD-MMM-YY')) as DATE,ID,Amount inline [ Date,ID,Amount 31-Oct-22,A1234,100 30-Nov-22,A4568,400 ]; NoConcatenate Main: Load Date(DATE + IterNo()-1) as DATE ,ID ,Amount ,DateTo WHILE floor(DATE + IterNo()-1) <= floor(DateTo) ; load * ,coalesce(Previous(DATE),DATE) as DateTo Resident temp Order by DATE Desc; Drop table temp; exit Script;

Re: Repeat data for next dates

nqlik123 — Sun, 25 Dec 2022 19:04:47 GMT

Sorry for the late reply, i tried the solution works fine for single id in single date, when i have multiple ids, i'm not able to get the right output

Re: Repeat data for next dates

nqlik123 — Sun, 25 Dec 2022 19:08:45 GMT

sorry for the late reply, My Id in November "A4568" is also appearing in October, can you help me with this i need the ids to flow to next dates but not to the previous dates

Re: Repeat data for next dates

vinieme12 — Tue, 27 Dec 2022 01:40:20 GMT

just make the below highlighted change instead of <= , change to <

WHILE floor(DATE + IterNo()-1) < floor(DateTo)

Re: Repeat data for next dates

RudyKostka — Mon, 02 Jan 2023 11:13:56 GMT

The solution corresponded to the task. If multiple IDs are present, it is necessary to complete the assignment. Individual IDs have validity FROM - TO or is it sufficient to CONCATE values from the ID field ?
E.g. :
31-Oct-22 A1234,A1235,A1236 100
30-Nov-22 A4568,A4569,A4570 400
or
31-Oct-22 A1234 100
31-Oct-22 A1235 200
31-Oct-22 A1236 300
30-Nov-22 A4568 400
30-Nov-22 A4569 500
30-Nov-22 A4570 400
or
ID, FROM, TO, VALUE
A1234, 31-Oct-22, 29-Oct-22, 100
A1235, 31-Oct-22, 29-Oct-22, 200
A1236, 31-Oct-22, 29-Oct-22, 300
etc.