Help on missing data

[bazzaonline]

Hello,

I have to very simple tables

Number     Name

1               Fred

2               Jen

3               Sam

4               Tom

and

Number     Happy

1               Yes

2               No

3

I need to end up with a Table that looks like below, what can I put in the script???

Number     Name         Happy

1               Fred          Yes        

2               Jen            No

3               Sam          Unknown

4               Tom          Unknown

[MK_QSL]

T1:

Mapping Load Number, Happy From....

T2:

Load Number, Name, ApplyMap('T1',Number,'Unknown') as Happy From ....

Hope this would help...!

[rubenmarin]

Hi Paul:

Map_Happy:

mapping LOAD * Where Len(Happy)>0;

LOAD * Inline [

    Number,Happy

    1,Yes

    2,No

    3,

];

Data:

LOAD *, ApplyMap('Map_Happy', Number, 'Unknown') as Happy

Inline [

    Number,Name

    1,Fred

    2,Jen

    3,Sam

    4,Tom

];

[bazzaonline]

Sorry, I probably wasn't very clear.  This is the script I have and need to amend to create the desired outcome;

LOAD Number,

          Name

FROM

(ooxml, embedded labels, table is Sheet1);

Left Join

LOAD Number,

          Happy

FROM

(ooxml, embedded labels, table is Sheet1);

[MK_QSL]

T1:

LOAD Number,

          Name

FROM

(ooxml, embedded labels, table is Sheet1);

Left Join

LOAD Number,

          Happy

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Final:

Load Number, Name, If(IsNull(Happy) or Len(Trim(Happy))=0, 'Unknown',Happy) as Happy

Resident T1;

Drop Table T1;

[rubenmarin]

Hi Paul, adpting our proposal to your script it canbe:

Map_Happy:

Mapping LOAD Number,

          Happy

FROM

(ooxml, embedded labels, table is Sheet1);

LOAD Number,

          Name,

          ApplyMap('Map_Happy', Number, 'Unknown') as Happy

FROM

(ooxml, embedded labels, table is Sheet1);

[bazzaonline]

Brilliant, thank you so much!!  Paul

[Anonymous]

Happys:
LOAD * INLINE [
Number, Happy
1, Yes
2, No
3
];

MappingHappy:
Mapping LOAD
Number,
Happy
Resident Happys;


Names:
LOAD *,
if(ApplyMap('MappingHappy',Number,'Unknown')='','Unknown',ApplyMap('MappingHappy',Number,'Unknown')) as Happy
;

LOAD * INLINE [
Number, Name
1, Fred
2, Jen
3, Sam
4, Tom
];


DROP Table Happys;
EXIT Script;
!