Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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' ???
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.
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];
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.
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
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.
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);
Thanks you, guys. Well done.
The last answers were helpful for my project.
You're welcome.
Glad I could be of hep.
Regards.