Continuous Value (Data)

[beck_bakytbek]

Hi Folks,
i got  a Question, i have my table:

ProjNr, Data, Employee, Amount

2001, 02.02.2017, 12, 20
2001, 04.02.2017, 12, 20
2001, 08.02.2017, 14, 10

Does anybody have any idea, how can i make my column: Data as continuous Data, i mean by that this output

ProjNr, Data, Employee, Amount, 

2001, 01.02.2017, -, -
2001, 02.02.2017, 12, 20
2001, 03.02.2017, -, -
2001, 04.02.2017, 12, 20
2001, 05.02.2017, -, -
2001, 06.02.2017, -, -
2001, 07.02.2017, -, -
2001, 08.02.2017, 14, 10

Does anybody have any idea how to resolve this issue?

Thanks a lot

Beck

[Vegar]

Try something like this. 

Data:
LOAD * inline [
ProjNr, Data, Employee, Amount
2001, 02.02.2017, 12, 20
2001, 04.02.2017, 12, 20
2001, 08.02.2017, 14, 10
];

For _date = date('02.02.2017') to  date('08.02.2017') 
  PrjDate:
  LOAD 
    dayname($(_date)) as Data
  AutoGenerate 1;
next

JOIN (PrjDate)
LOAD Distinct  
   ProjNr
Resident 
  Data;

JOIN (Data)
LOAD 
  *  
RESIDENT 
  PrjDate
;

DROP TABLE PrjDate;

[beck_bakytbek]

Hi Vegar,

thanks a lot for your Feedback, i want ask:  this part " For _date = date('02.02.2017') to  date('08.02.2018') " can i make that dynamically or should i Always type my time intervall? 

[beck_bakytbek]

Hi Vegar,

or it is possible to make this structure:

ProjNr, Data, Employee, Amount

2001, 01.02.2017, 12, 20
2001, 02.02.2017, 12, 20
2001, 03.02.2017, 12, 20
2001, 04.02.2017, 12, 20
2001, 05.02.2017, 12, 20
2001, 06.02.2017, 12, 20
2001, 07.02.2017, 12, 20
2001, 08.02.2017, 14, 10
2001, 09.02.2017, 14, 10

if make the constinouos data, then i can take for instance Employ 12 and Amount 20 until 08.02.2017 and from 08.02.2017 i take the employee 14 and Amount 10

[Vegar]

The date interval was a quick solution, off course you can do that dynamically.
MinMaxDate:
Load
min(Date) As MinDate
max(Date) As MaxDate
resident MyLoadedData
LET vMinDate = Peek('MinDate');
LET vMaxDate = Peek('MaxDate');
Drop table MinMaxDate;

For _date = $(vMinDate) to $(vMaxDate)
$(vMinDate)
PrjDate:
LOAD
dayname($(_date)) as Data
AutoGenerate 1;
next

[Vegar · Accepted solution]

When you have this data table:

ProjNr, Data, Employee, Amount,
2001, 01.02.2017, -, -
2001, 02.02.2017, 12, 20
2001, 03.02.2017, -, -
2001, 04.02.2017, 12, 20
2001, 05.02.2017, -, -
2001, 06.02.2017, -, -
2001, 07.02.2017, -, -
2001, 08.02.2017, 14, 10

You can do this...

Load
ProjNr,
Data,
Employee,
If( Peek('Employee') = Employee, 
Peek('Amount') + Amount, 
  Amount ) as Amount
Resident DataTable
Order by Employee, Data ;

[beck_bakytbek]

Hi Vegar,

thanks a lot for your help and time, i will implement it and will report about results

[Vegar]

There where added some strange * symbols around rows that I tried to mark as bold in my previous reply (reply by mail).
I've made an edit to it and it should look better now.

[beck_bakytbek]

Hi Vegar,

first of all, thanks a lot for your time and help, have a nice day 😃

[beck_bakytbek]

Hi Vegar,

i have a question: if i use above solution that consists of 2 part: 1. Transformation of Data and 2. Peek(), so i have any deviation with peek(), then the peek()function does not work. or am i missing something?

Thanks a lot