topic Re: Help on missing data in QlikView

Help on missing data

bazzaonline — Thu, 01 Oct 2015 14:18:02 GMT

Hello,

I have to very simple tables

Number Name

1 Fred

2 Jen

3 Sam

4 Tom

and

Number Happy

1 Yes

2 No

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

Re: Help on missing data

MK_QSL — Thu, 01 Oct 2015 14:20:57 GMT

T1:

Mapping Load Number, Happy From....

T2:

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

Hope this would help...!

Re: Help on missing data

rubenmarin — Thu, 01 Oct 2015 14:25:03 GMT

Hi Paul:

Map_Happy:

mapping LOAD * Where Len(Happy)>0;

LOAD * Inline [

Number,Happy

1,Yes

2,No

];

Data:

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

Inline [

Number,Name

1,Fred

2,Jen

3,Sam

4,Tom

];

Re: Help on missing data

bazzaonline — Thu, 01 Oct 2015 14:35:31 GMT

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

Re: Help on missing data

MK_QSL — Thu, 01 Oct 2015 14:37:52 GMT

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;

Re: Help on missing data

rubenmarin — Thu, 01 Oct 2015 14:38:43 GMT

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

Re: Help on missing data

bazzaonline — Thu, 01 Oct 2015 14:55:42 GMT

Brilliant, thank you so much!! Paul

Re: Help on missing data

Anonymous — Thu, 01 Oct 2015 15:44:18 GMT

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