Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Assigning values to null

Hello there!

I want to assign some values to null.

Here is my sample data;   

TPNameTpcodeTpcode2
Bmghnn Rogw91A91A
Bmghnn Rogw598<Unknown>
Bmghnn Rogw92A92A
Bmghnn Rogw987<Unknown>
Bmghnn Rogw95C95C
Bmghnn Rogw2298<Unknown>
Bmghnn Rogw3432<Unknown>
Bmghnn Rogw34<Unknown>
Bmghnn Rogw43<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;

   

TPNameTpcodeTpcode2
Bmghnn Rogw91A91A
Bmghnn Rogw598<Unknown>
Bmghnn Rogw92A92A
Bmghnn Rogw987<Unknown>
Bmghnn Rogw95C95C
Bmghnn Rogw2298<Unknown>
Bmghnn Rogw3432<Unknown>
Bmghnn Rogw34<Unknown>
Bmghnn Rogw43<Unknown>
Bmghnn Rogw93B0
Bmghnn Rogw94D0

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

1 Solution

Accepted Solutions
Highlighted
Specialist III
Specialist III

Re: Assigning values to null

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

Screenshot_1.jpg

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

];

Screenshot_1.jpg

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.

View solution in original post

9 Replies
Highlighted
Creator II
Creator II

Re: Assigning values to null

Hi,

Kindly upload your sample QVW.

Highlighted
Partner
Partner

Re: Assigning values to null

Highlighted
MVP
MVP

Re: Assigning values to null

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Not applicable

Re: Assigning values to null

Hi Dinesh,

Sample QVW attached

Highlighted
Not applicable

Re: Assigning values to null

Hi Jonathan,

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

Highlighted
Specialist III
Specialist III

Re: Assigning values to null

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:

Screenshot_1.jpg

Highlighted
Creator III
Creator III

Re: Assigning values to null

Hi Enthu,

Could you pls explain me your requirement little more elaborate?

Regards

Hemanth

Highlighted
Not applicable

Re: Assigning values to null

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!

Highlighted
Specialist III
Specialist III

Re: Assigning values to null

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

Screenshot_1.jpg

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

];

Screenshot_1.jpg

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.

View solution in original post