1

I'm trying to populate missing values of the dates in between with peek function. I'm used the following code but still I couldn't get the results I'm looking for

Table:
LOAD * INLINE [
    Date, Article, Quantity
    01/02/2021, A, 10
    03/02/2021, A, 20
    06/02/2021, B, 30
    07/02/2021, C, 40
];

Join(Table)
LOAD
    Date(MinDate+IterNO ()-1) as Date
While
  (MinDate+IterNO ()-1) <= Num(MaxDate -1)
;

LOAD
  Min(Date) as MinDate,
  Max(Date) As MaxDate
Resident 
  Table
;

Join(Table)
Load
  If( Len(Quantity) = 0, peek('newQuantity'), Quantity) as newQuantity
Resident 
  Table
;

results

Thank you so much for your help

Stefan Stoichev
  • 4,615
  • 3
  • 31
  • 51
walid
  • 13
  • 2

1 Answers1

0

I tried a workaround

SET ThousandSep=' ';
SET DecimalSep=',';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep=',';
SET MoneyFormat='# ##0,00 €;-# ##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';
SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';
A:
LOAD * ,
Date(Date) as Date1
;

LOAD * INLINE [
    Date, Article, Quantity
    01/02/2021, A, 10
    01/02/2021, B, 12
    03/02/2021, A, 20
    06/02/2021, B, 30
    07/02/2021, C, 40
];

//required for generating calendar for all the months
maxMin:
LOAD Date(Max(Date1)) as maxdate,
Date(min(Date1)) as mindate
Resident A;

LET vStartDate = num(Peek('mindate',0,'maxMin'));
LET vEndDate = num(Peek('maxdate',0,'maxMin'));


//Calendar to generate all the months
 Calendar:

LOAD
Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,
RangeSum(Peek('RowNum'), 1) AS RowNum
AutoGenerate vEndDate - vStartDate + 1;

///Joining the Existing date with the calendar generated months 
B:
LOAD  
Date(max(Date)) as Date,
RowNo() as rowno
Resident Calendar
Group By Date
;
left Join 
LOAD Date,Article,Quantity
Resident A;



//Final table
NoConcatenate
C:
LOAD Date,
rowno,
if(IsNull(Article),Peek(Article),Article) as Article,
if(IsNull(Quantity),Peek(Quantity),Quantity) as Quantity
Resident B
Order by rowno;

DROP Tables A,Calendar,B,maxMin;

The problem is that i need to show for each date all articles with the previous quantity if not then zero, the program only populate the missing date with last article and quantity as shown below : [![results][1]][1] What i'm looking for is like for date 01/02/2021 :

Date Article Quantity
01/02/2021 A 10
01/02/2021 B 12
01/02/2021 C 0
02/02/2021 A 10
02/02/2021 B 12
02/02/2021 C 0
03/02/2021 A 20
03/02/2021 B 12
03/02/2021 C 0

Basically I need to aggregate by article or something i just don't know how to do it, Thank you for your help. [1]: https://i.stack.imgur.com/VJEJi.png

walid
  • 13
  • 2