topic Re: Missing value in table script in QlikView

Missing value in table script

Wed, 25 Nov 2020 16:16:04 GMT

Dear all,

I have tried to assign misisng value in table with Unknown, but the script does not work.

SAP:
LOAD * INLINE
[
Group Name, Value
GA1234, 2897
GA4567, 7000
GA9831 ,1200
GA5671,9400
];

POLFA:
LOAD * INLINE
[
Group Name
GA1234,
GA4567,
GA9831
];

Group:
LOAD * INLINE [
Group
POLFA
]
;

JOIN (Group)
LOAD
[Group Name]
Resident POLFA
;

DROP Table POLFA;

LOAD
[Group Name],
if(Group = '-', 'Unknown', Group) as Group /*This does not work*/
Resident Group;

The problem is that GA9831 cannot be assigned with 'Unknown'.

Could you please give me an advice how to handle this?

Best regards,

Cornelis

Re: Missing value in table script

Chanty4u — Sun, 13 Mar 2016 07:25:39 GMT

try dis

LOAD

[Group Name],

Replace(Group,'-', 'unknown') as Group

Re: Missing value in table script

Kushal_Chawda — Sun, 13 Mar 2016 08:04:46 GMT

Try to create the additional join with SAP for condition to work.

SAP:

LOAD * INLINE

[

Group Name, Value

GA1234, 2897

GA4567, 7000

GA9831 ,1200

GA5671,9400

];

PLFA:

LOAD * INLINE

[

Group Name

GA1234,

GA4567,

GA9831

];

Group:

LOAD * INLINE [

Group

POLFA

]

;

JOIN (Group)

LOAD

[Group Name]

Resident PLFA

;

DROP Table PLFA;

Left Join (SAP)

LOAD *

Resident Group;

DROP Table Group;

Group:

NoConcatenate

LOAD [Group Name],

Value,

if(len(trim(Group))<=0, 'Unknown', Group) as Group /*This does not work*/

Resident SAP;

DROP Table SAP;

Re: Missing value in table script

Digvijay_Singh — Sun, 13 Mar 2016 08:20:15 GMT

Right Join (Group)

Load *

Resident SAP;

Drop table SAP;

NoConcatenate

Final:

LOAD

[Group Name],Value,

if(len(Group)<=0, 'Unknown', Group) as Group

Resident Group;

Drop table Group;

Re: Missing value in table script

Digvijay_Singh — Sun, 13 Mar 2016 08:20:51 GMT

Replace above script after DROP Table POLFA;

Re: Missing value in table script

Mon, 14 Mar 2016 05:57:00 GMT

Yes, that is the right answer.

Concatenate does not provide the solution, it must be the JOIN statement.

Thank you for your help, very useful!

Re: Missing value in table script

Mon, 14 Mar 2016 05:59:25 GMT

Dear Chanty 4u,

Thanks you for your swift reply.

Unfortunately, it does not help but teh Replace function is something that I have not come up.

Initially, I thought ISNULL is teh solution, but it does not help, either.

Best regards,

Re: Missing value in table script

Kushal_Chawda — Mon, 14 Mar 2016 06:01:05 GMT

I am glad that it was helpful

Re: Missing value in table script

Mon, 14 Mar 2016 06:01:19 GMT

Yes, you and Kush deliver me the solution where I'm looking for.

The JOIN statement is key to handle.

Best regards,

Re: Missing value in table script

Digvijay_Singh — Mon, 14 Mar 2016 06:25:20 GMT

Glad to hear that.

Re: Missing value in table script

Mon, 14 Mar 2016 06:29:16 GMT

Good to hear from you.

It sounds encouraging that there are people that are willing to help me on way, like you.

Have a great day.

Re: Missing value in table script

Mon, 14 Mar 2016 06:29:45 GMT

Good to hear from you.

It sounds encouraging that there are people that are willing to help me on way, like you and Kush..

Have a great day.