Assigning values to null

[#1]

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?

[dineshm030]

Hi,

Kindly upload your sample QVW.

[shraddha_g]

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

[jonathandienst]

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.

[#5]

Hi Dinesh,

Sample QVW attached

[#6]

Hi Jonathan,

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

[MindaugasBacius]

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:

[hemanthaanichet]

Hi Enthu,

Could you pls explain me your requirement little more elaborate?

Regards

Hemanth

[#9]

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!

[MindaugasBacius · Accepted solution]

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.