Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 ;
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;
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?
Hi Vegar,
or it is possible to make this structure:
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 ;
Hi Vegar,
thanks a lot for your help and time, i will implement it and will report about results
Hi Vegar,
first of all, thanks a lot for your time and help, have a nice day 😃
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