Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have some data that i am loading in,
Name
Dim1
dim2
Date
Value
i want to add a field in the script that will show me the difference in values for each name by date.
e.g.
Name | Dim1 | Dim2 | Dim3 | Dim4 | 25/06/20 | 26/06/20 | 27/06/20 |
Value1 | 199,448 | 199 | 0.00 | 0 | 748 | 749 | 749 |
Value2 | 175,729 | 176 | 0.00 | 0 | 937 | 938 | 938 |
Value3 | 121,676 | 122 | 0.00 | 0 | 372 | 373 | 374 |
so the new field will contain the following (if 25/06/20 was the first date in the data)
Name | 25/06/20 | 26/06/20 | 27/06/20 |
Value1 | 748 | 1 | 0 |
Value2 | 937 | 1 | 0 |
Value3 | 372 | 1 | 1 |
I know i would have to use the peek function but i'm a bit lost.
Can anyone help please?
I have attached some files.
Thanks 🙂
hi
this script should work for you
Data_TMP:
CrossTable(Date, Data, 5)
LOAD *
FROM
[C:\Users\Liron\Downloads\Test_Data.xlsx]
(ooxml, embedded labels, table is Data)
where RowNo()<=29;
Data:
Load
Name,
Dim1,
Dim2,
Dim3,
Dim4,
Date,
Data,
if(Previous(Name)=Name,Data-previous(Data),Data) as DateDiff
Resident Data_TMP
order by Name , Date;
drop table Data_TMP;
hi
this script should work for you
Data_TMP:
CrossTable(Date, Data, 5)
LOAD *
FROM
[C:\Users\Liron\Downloads\Test_Data.xlsx]
(ooxml, embedded labels, table is Data)
where RowNo()<=29;
Data:
Load
Name,
Dim1,
Dim2,
Dim3,
Dim4,
Date,
Data,
if(Previous(Name)=Name,Data-previous(Data),Data) as DateDiff
Resident Data_TMP
order by Name , Date;
drop table Data_TMP;
fantastic, thanks very much 🙂