Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Filling missing row with Scripting

Hi, all,

I'm stuck with this. We take these two tabs:

TAB1:

load * inline [

country, points

Brazil, 234

US, 432

Germany, 121

India, 444];

TAB2:

load * inline [

country, points

US, 412

Germany, 488

India, 224];

Note that Brazil will not appear into TAB2 because it didn't score points. But I need to see it there showing 0 point.

So, how I do create  a row into TAB2 with: 'Brazil, 0' ???

Tags (4)
1 Solution

Accepted Solutions
bruno_montenegr
Contributor III

Re: Filling missing row with Scripting

Hi, Bernardo.

You can do something  like this:

TAB1:

load * inline [

country, points

Brazil, 234

US, 432

Germany, 121

India, 444];

TAB2:

NoConcatenate load * inline [

country, points

US, 412

Germany, 488

India, 224];

Outer Join (TAB2) Load

  country

Resident TAB1;

TAB2_Definitive:

NoConcatenate Load

  country,

  if(IsNull(points), 0, points) as points

Resident TAB2;


Drop Table TAB2;

That is just a guide. Attached is a sample app. Notice the synthetic you should avoid.

Cheers.

View solution in original post

7 Replies
Not applicable

Re: Filling missing row with Scripting

TAB1:

load * inline [

country, points

Brazil, 234

US, 432

Germany, 121

India, 444];

TAB2:

load * inline [

country, points

Brazil, 0

US, 412

Germany, 488

India, 224];

Not applicable

Re: Filling missing row with Scripting

Ok, hehe.

Now imagine another table with more than 300 countries and scores by month.

I can not do this by hand.

I need a way I can generate hundreds of these missing countries.

Not applicable

Re: Filling missing row with Scripting

Hmmmm well in that case I'm trying to think of a way you could choose a way to join those inline tables to a table with scores of all zeros so that you only add the zero entry if there is no other entry

bruno_montenegr
Contributor III

Re: Filling missing row with Scripting

Hi, Bernardo.

You can do something  like this:

TAB1:

load * inline [

country, points

Brazil, 234

US, 432

Germany, 121

India, 444];

TAB2:

NoConcatenate load * inline [

country, points

US, 412

Germany, 488

India, 224];

Outer Join (TAB2) Load

  country

Resident TAB1;

TAB2_Definitive:

NoConcatenate Load

  country,

  if(IsNull(points), 0, points) as points

Resident TAB2;


Drop Table TAB2;

That is just a guide. Attached is a sample app. Notice the synthetic you should avoid.

Cheers.

View solution in original post

anbu1984
Honored Contributor III

Re: Filling missing row with Scripting

TAB1:

load * inline [

country, points

Brazil, 234

US, 432

Germany, 121

India, 444];

TAB2:

load country1, points1 inline [

country1, points1

US, 412

Germany, 488

India, 224];

Concatenate

Load country as country1, 0 as points1 Resident TAB1 Where Not Exists(country1,country);

Not applicable

Re: Filling missing row with Scripting

Thanks you, guys. Well done.

The last answers were helpful for my project.

bruno_montenegr
Contributor III

Re: Filling missing row with Scripting

You're welcome.

Glad I could be of hep.

Regards.