topic Re: How to replace nulls based on a session in QlikView

How to replace nulls based on a session

Anonymous — Wed, 09 Nov 2016 10:53:12 GMT

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

Re: How to replace nulls based on a session

sunny_talwar — Wed, 09 Nov 2016 10:56:13 GMT

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?

Re: How to replace nulls based on a session

Anonymous — Wed, 09 Nov 2016 10:59:52 GMT

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

Re: How to replace nulls based on a session

sunny_talwar — Wed, 09 Nov 2016 11:02:23 GMT

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

Re: How to replace nulls based on a session

sunny_talwar — Wed, 09 Nov 2016 11:08:58 GMT

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, 46f8e37f-e70d, NULL, Not relavent

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, 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;

Re: How to replace nulls based on a session

Anonymous — Wed, 09 Nov 2016 11:09:11 GMT

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

Re: How to replace nulls based on a session

Anonymous — Wed, 09 Nov 2016 11:44:04 GMT

Hi Sunny

Can i send a PM please?

Thanks

Re: How to replace nulls based on a session

sunny_talwar — Wed, 09 Nov 2016 12:20:53 GMT

Sure, go for it