topic Re: Assigning values to null in QlikView

Assigning values to null

Tue, 22 Nov 2016 05:46:48 GMT

Hello there!

I want to assign some values to null.

Here is my sample data;

TPName	Tpcode	Tpcode2
Bmghnn Rogw	91A	91A
Bmghnn Rogw	598	<Unknown>
Bmghnn Rogw	92A	92A
Bmghnn Rogw	987	<Unknown>
Bmghnn Rogw	95C	95C
Bmghnn Rogw	2298	<Unknown>
Bmghnn Rogw	3432	<Unknown>
Bmghnn Rogw	34	<Unknown>
Bmghnn Rogw	43	<Unknown>

I am using a if(match) statement to assign values to Tpcode2 column. the statement I am using is;

If(Match(Tpcode, '91A', '92A','93B', '94D','95C'), (Tpcode) as Tpcode2

In this case TPName does not have '93B', '94D'. Is there a way where I can generate or do something so that the outcome is as below;

TPName	Tpcode	Tpcode2
Bmghnn Rogw	91A	91A
Bmghnn Rogw	598	<Unknown>
Bmghnn Rogw	92A	92A
Bmghnn Rogw	987	<Unknown>
Bmghnn Rogw	95C	95C
Bmghnn Rogw	2298	<Unknown>
Bmghnn Rogw	3432	<Unknown>
Bmghnn Rogw	34	<Unknown>
Bmghnn Rogw	43	<Unknown>
Bmghnn Rogw	93B	0
Bmghnn Rogw	94D	0

Or is there any way that I can just assign these 2 codes to the TPName?

Re: Assigning values to null

dineshm030 — Tue, 22 Nov 2016 06:05:47 GMT

Hi,

Kindly upload your sample QVW.

Re: Assigning values to null

shraddha_g — Tue, 22 Nov 2016 06:14:49 GMT

Try from How to display NULL values as 'NULL' instead of '-'

Re: Assigning values to null

jonathandienst — Tue, 22 Nov 2016 06:20:47 GMT

You will have to concatenate these two values to the appropriate table during the load. You cannot easily add extra data rows to a table from the front end.

Re: Assigning values to null

Tue, 22 Nov 2016 06:21:44 GMT

Hi Dinesh,

Sample QVW attached

Re: Assigning values to null

Tue, 22 Nov 2016 06:23:09 GMT

Hi Jonathan,

I am a beginner. Can you please show how to do that?

Re: Assigning values to null

MindaugasBacius — Tue, 22 Nov 2016 06:47:24 GMT

I suppose I would do like this:

Table:

LOAD TPName,

Tpcode,

Tpcode2

FROM

[https://community.qlik.com/thread/240545]

(html, codepage is 1257, embedded labels, table is @1);

tmp:

LOAD Distinct TPName

FROM

[https://community.qlik.com/thread/240545]

(html, codepage is 1257, embedded labels, table is @1);

Join (tmp)

LOAD *

INLINE [

Code

91A

92A

93B

94D

95C

];

Concatenate (Table)

LOAD TPName

,Code as Tpcode

Resident tmp

Where Not Exists (Tpcode, Code)

;

DROP Tables tmp;

The result are:

Re: Assigning values to null

hemanthaanichet — Tue, 22 Nov 2016 07:35:19 GMT

Hi Enthu,

Could you pls explain me your requirement little more elaborate?

Regards

Hemanth

Re: Assigning values to null

Tue, 22 Nov 2016 08:01:35 GMT

Hi Mindaugas,

thanks so much for the detailed reply.

I tried this solution and it seems to be working only if i do not use the where not exists statement and if I do not drop the temp table, not sure why.

Could you please explain the logical flow in layman language to my understanding?

Thanks!

Re: Assigning values to null

MindaugasBacius — Tue, 22 Nov 2016 08:29:55 GMT

In your case you're adding extra data (new rows, not columns):

By using the script below you're synthetically creating the table:

tmp:

LOAD Distinct TPName

FROM

[https://community.qlik.com/thread/240545]

(html, codepage is 1257, embedded labels, table is @1);

Join (tmp)

LOAD *

INLINE [

Code

91A

92A

93B

94D

95C

];

The table contains all the values you wish to check whether or not they are in your original table.

Then by using script below you're concatenating the original Table and the previously mentioned table:

Concatenate (Table)

LOAD TPName

,Code as Tpcode

Resident tmp

Where Not Exists (Tpcode, Code)

;

Pay attention to the Where clause. It removes all the existing rows and leaves only ones you're expecting to add.