topic Re: Prob in Script in QlikView

Prob in Script

nikhilgarg — Wed, 08 Apr 2015 10:42:32 GMT

Hey,

I have script as:

Table1:

Load SalesPersonId;

Left Join

Table2:

LOAD SalesPersonId,

Region,

Salespersonname

LeftJoin

Table3:

LOAD SalesPersonId,

Desk;

Now i want those people that dont have region and desk it should come as 'Unknown' for both. How to acheive this?

Thanks

Re: Prob in Script

PrashantSangle — Wed, 08 Apr 2015 10:56:00 GMT

Hi,

Table1:

Load SalesPersonId;

Left Join

LOAD SalesPersonId,

Region,

Salespersonname

LeftJoin

LOAD SalesPersonId,

Desk;

final_Table:

Load *,

"Unknown" as Flag,

Resident Table1

where Isnull(Region) and Isnull(Desk);

concatenate

Load *,

"known" as Flag,

Resident Table1

where not Isnull(Region) and not Isnull(Desk);

drop table Table1;

Regards

Re: Prob in Script

nikhilgarg — Wed, 08 Apr 2015 11:09:13 GMT

Hey ,

Why u have used unknown and known as flag?

I want if salespersonid matching den values of region and desk should be

displayed if region and desk values not there den it should ahow unknown

only

Re: Prob in Script

awhitfield — Wed, 08 Apr 2015 11:12:57 GMT

Hi Nikhil

Table1:

Load SalesPersonId
FROM
[159247.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join
Table2:
LOAD SalesPersonId,
if(Len(Region)=0,'Unknown',Region) as Region,
Salespersonname
FROM
[159247.xlsx]
(ooxml, embedded labels, table is Sheet2);
Left Join
Table3:
LOAD SalesPersonId,
If(Len(Desk)=0,'Unknown', Desk) as Desk
FROM
[159247.xlsx]
(ooxml, embedded labels, table is Sheet3);

Regards

Andy

Re: Prob in Script

PradeepReddy — Wed, 08 Apr 2015 11:15:38 GMT

Please check the attachment. It might be helpful.

Re: Prob in Script

maxgro — Wed, 08 Apr 2015 11:17:30 GMT

add this after your script

Table2:

NoConcatenate load

SalesPersonId,

if(IsNull(Region), 'unknown', Region) as Region,

if(IsNull(Salespersonname), 'unknown', Salespersonname) as Salespersonname,

if(IsNull(Desk), 'unknown', Desk) as Desk

Resident

Table1;

DROP Table Table1;