Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to replace nulls based on a session

Hi All

I have data like the following

  

DatesessionGuid
08/11/20164af7-42b9NULL
08/11/20164af7-42b9NULL
08/11/20164af7-42b9NULL
08/11/20164af7-42b9NULL
08/11/20164af7-42b9NULL
08/11/20164af7-42b9NULL
08/11/20164af7-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

7 Replies
sunny_talwar

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?

Anonymous
Not applicable
Author

Hi Sunny,

See all 7 rows with guid

Here you go, Thanks

   

DatesessionGuid
08/11/201651bc9db1-acf4-45ccNULLNot relavent
08/11/201651bc9db1-acf4-45ccNULLNot relavent
08/11/201646f8e37f-e70dNULLNot relavent
08/11/201646f8e37f-e70dNULLNot relavent
08/11/201646f8e37f-e70dNULLNot relavent
08/11/201646f8e37f-e70dNULLNot relavent
08/11/201646f8e37f-e70dNULLNot relavent
08/11/201646f8e37f-e70dNULLNot relavent
08/11/201646f8e37f-e70dNULLNot relavent
08/11/201646f8e37f-e70dNULLNot relavent
08/11/201646f8e37f-e70dNULLNot relavent
08/11/201646f8e37f-e70dNULLNot relavent
08/11/201646f8e37f-e70dNULLNot relavent
08/11/201646f8e37f-e70dNULLNot relavent
08/11/2016936b-9677f9120efdNULLNeed Grouping
08/11/2016936b-9677f9120efdNULLNeed Grouping
08/11/2016936b-9677f9120efdNULLNeed Grouping
08/11/2016936b-9677f9120efdNULLNeed Grouping
08/11/2016936b-9677f9120efdNULLNeed Grouping
08/11/2016936b-9677f9120efd3c4be01e7b4fNeed Grouping
sunny_talwar

So, all of these would be acquire the same Guid, rest everything will be NULL, right?

Capture.PNG

sunny_talwar

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;

Anonymous
Not applicable
Author

Rest would be Null if the session ids are not the same

Anonymous
Not applicable
Author

Hi Sunny

Can i send a PM please?

Thanks

sunny_talwar

Sure, go for it