Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have data like the following
Date | session | Guid |
08/11/2016 | 4af7-42b9 | NULL |
08/11/2016 | 4af7-42b9 | NULL |
08/11/2016 | 4af7-42b9 | NULL |
08/11/2016 | 4af7-42b9 | NULL |
08/11/2016 | 4af7-42b9 | NULL |
08/11/2016 | 4af7-42b9 | NULL |
08/11/2016 | 4af7-42b9 | 4a51-a981 |
I want for each session to replace the null with the Guid (Group them basically)
There can be some Sessions with no relevant Guid which i want to exclude
Any ideas how to do this?
Thanks
You want to ultimately see a single row or do you want to see all the 7 rows with just the same Guid? Also, can you add some more data to see what the logic is when there are two different Guid for a single combination of Date and session? or that can never happen?
Hi Sunny,
See all 7 rows with guid
Here you go, Thanks
Date | session | Guid | |
08/11/2016 | 51bc9db1-acf4-45cc | NULL | Not relavent |
08/11/2016 | 51bc9db1-acf4-45cc | NULL | Not relavent |
08/11/2016 | 46f8e37f-e70d | NULL | Not relavent |
08/11/2016 | 46f8e37f-e70d | NULL | Not relavent |
08/11/2016 | 46f8e37f-e70d | NULL | Not relavent |
08/11/2016 | 46f8e37f-e70d | NULL | Not relavent |
08/11/2016 | 46f8e37f-e70d | NULL | Not relavent |
08/11/2016 | 46f8e37f-e70d | NULL | Not relavent |
08/11/2016 | 46f8e37f-e70d | NULL | Not relavent |
08/11/2016 | 46f8e37f-e70d | NULL | Not relavent |
08/11/2016 | 46f8e37f-e70d | NULL | Not relavent |
08/11/2016 | 46f8e37f-e70d | NULL | Not relavent |
08/11/2016 | 46f8e37f-e70d | NULL | Not relavent |
08/11/2016 | 46f8e37f-e70d | NULL | Not relavent |
08/11/2016 | 936b-9677f9120efd | NULL | Need Grouping |
08/11/2016 | 936b-9677f9120efd | NULL | Need Grouping |
08/11/2016 | 936b-9677f9120efd | NULL | Need Grouping |
08/11/2016 | 936b-9677f9120efd | NULL | Need Grouping |
08/11/2016 | 936b-9677f9120efd | NULL | Need Grouping |
08/11/2016 | 936b-9677f9120efd | 3c4be01e7b4f | Need Grouping |
So, all of these would be acquire the same Guid, rest everything will be NULL, right?
Try this:
Table:
LOAD Date,
session,
Guid,
If(Guid = 'NULL', 0, 1) as Flag
INLINE [
Date, session, Guid, Temp
08/11/2016, 51bc9db1-acf4-45cc, NULL, Not relavent
08/11/2016, 51bc9db1-acf4-45cc, NULL, Not relavent
08/11/2016, 46f8e37f-e70d, NULL, Not relavent
08/11/2016, 46f8e37f-e70d, NULL, Not relavent
08/11/2016, 46f8e37f-e70d, NULL, Not relavent
08/11/2016, 46f8e37f-e70d, NULL, Not relavent
08/11/2016, 46f8e37f-e70d, NULL, Not relavent
08/11/2016, 46f8e37f-e70d, NULL, Not relavent
08/11/2016, 46f8e37f-e70d, NULL, Not relavent
08/11/2016, 46f8e37f-e70d, NULL, Not relavent
08/11/2016, 46f8e37f-e70d, NULL, Not relavent
08/11/2016, 46f8e37f-e70d, NULL, Not relavent
08/11/2016, 46f8e37f-e70d, NULL, Not relavent
08/11/2016, 46f8e37f-e70d, NULL, Not relavent
08/11/2016, 936b-9677f9120efd, NULL, Need Grouping
08/11/2016, 936b-9677f9120efd, NULL, Need Grouping
08/11/2016, 936b-9677f9120efd, NULL, Need Grouping
08/11/2016, 936b-9677f9120efd, NULL, Need Grouping
08/11/2016, 936b-9677f9120efd, NULL, Need Grouping
08/11/2016, 936b-9677f9120efd, 3c4be01e7b4f
08/11/2016, 4af7-42b9, NULL
08/11/2016, 4af7-42b9, NULL
08/11/2016, 4af7-42b9, NULL
08/11/2016, 4af7-42b9, NULL
08/11/2016, 4af7-42b9, NULL
08/11/2016, 4af7-42b9, NULL
08/11/2016, 4af7-42b9, 4a51-a981
];
FinalTable:
LOAD RowNo() as Key,
Date,
session,
If(Date = Previous(Date) and session = Previous(session), Peek('Guid'), Guid) as Guid
Resident Table
Order By Date, session, Flag desc;
DROP Table Table;
Rest would be Null if the session ids are not the same
Hi Sunny
Can i send a PM please?
Thanks
Sure, go for it