topic Re: How to replace with NULL when there is different data in column values? in QlikView

How to replace with NULL when there is different data in column values?

Thu, 27 Nov 2014 11:34:26 GMT

Hi,

I have to replace with NULL when there is different 'Point' for different 'number' and different 'Point' for same 'number'. How can I set this in script.

Please can anyone suggest me.

Thanks.

Re: How to replace with NULL when there is different data in column values?

sushil353 — Thu, 27 Nov 2014 11:36:04 GMT

Hi,

Can u share some sample data

Re: How to replace with NULL when there is different data in column values?

Thu, 27 Nov 2014 11:40:05 GMT

Please see attached.

Re: How to replace with NULL when there is different data in column values?

PrashantSangle — Thu, 27 Nov 2014 11:48:37 GMT

Hi,

Create Flag in script and use that flag in your set analysis.

Regards,

Re: How to replace with NULL when there is different data in column values?

Fri, 28 Nov 2014 09:34:43 GMT

Thanks. can you suggest how to create flag and proceed with set analysis please?

Re: How to replace with NULL when there is different data in column values?

vikasmahajan — Fri, 28 Nov 2014 09:50:02 GMT

PFA

vikas

Re: Re: How to replace with NULL when there is different data in column values?

jonathandienst — Fri, 28 Nov 2014 09:53:05 GMT

This code will create the flag field (assuming ID is a unique key for the data):

// Get the number values that are associated with more than one distinct Point value

T_Numbers:

LOAD number,

Count(ID) As Rows,

Count(DISTINCT Point) As Count,

Concat(DISTINCT ID, '|') As IDList

Resident Data

Group By number;

// Get the Point values that are associated with more than one distinct number value

T_Points:

LOAD Point,

Count(ID) As Rows,

Count(DISTINCT number) As Count,

Concat(DISTINCT ID, '|') As IDList

Resident Data

Group By Point;

// Get the list of IDs that need to be flagged

T_Flag:

LOAD SubField(IDList, '|') As ID

Resident T_Numbers

WHERE Rows >= 2 And Count >= 2;

// Get the list of IDs that need to be flagged

Concatenate(T_Flag)

LOAD SubField(IDList, '|') As ID

Resident T_Points

WHERE Rows >= 2 And Count >= 2;

// Add the flag field to your data table (1 = flag, null = no flag)

Left Join (Data)

LOAD ID,

1 As Flag

Resident T_Flag;

// Clean up

DROP Table T_Numbers, T_Points, T_Flag;

HTH

Jonathan

Re: How to replace with NULL when there is different data in column values?

jyothish8807 — Fri, 28 Nov 2014 09:59:14 GMT

Can you please explain your requirement a bit more.

Regards

Re: How to replace with NULL when there is different data in column values?

Fri, 28 Nov 2014 10:06:17 GMT

Thanks. I checked it. I need when there is same point the point should appear in column. when there is different point for same number It should replace with NULL

see please output and I need like this

name	number	point	output point
XX	0.01	L1003647	NULL
XX	0.01	L763753	NULL
XX	11.81	L1003647	L1003647
XX	49.64	L1003647	L1003647
XX	10.94	L1003647	NULL
XX	10.94	L61437	NULL

would it be possible?

Re: How to replace with NULL when there is different data in column values?

Fri, 28 Nov 2014 10:11:01 GMT

Thanks.

I have to replace with NULL when there is different 'Point' for different 'number' and different 'Point' for same 'number'. and when it is same point for different number the point should remain as it is. would it be possible

name	number	point	output point
XX	0.01	L1003647	NULL
XX	0.01	L763753	NULL
XX	11.81	L1003647	L1003647
XX	49.64	L1003647	L1003647
XX	10.94	L1003647	NULL
XX	10.94	L61437	NULL

Re: How to replace with NULL when there is different data in column values?

Fri, 28 Nov 2014 12:22:04 GMT

Any help please

Re: How to replace with NULL when there is different data in column values?

Fri, 28 Nov 2014 12:24:48 GMT

Thanks. I tried it still showing same data. Please help how to set this.