Filling missing row with Scripting

[#1]

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' ???

[#2]

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];

[#3]

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.

[#4]

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

[Anonymous · Accepted solution]

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.

[anbu1984]

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);

[#7]

Thanks you, guys. Well done.

The last answers were helpful for my project.

[Anonymous]

You're welcome.

Glad I could be of hep.

Regards.