topic Need your support in QlikView

Need your support

Thu, 01 May 2014 14:42:31 GMT

Hi experts,

I'm new to qlikview and I've attached snapshots both source and business intelligence plan.

We are getting an error while trying these.

I request to help me in detail if anyone knows.

Re: Need your support

MK_QSL — Thu, 01 May 2014 15:07:20 GMT

What type of error you are getting?

Re: Need your support

Thu, 01 May 2014 16:11:51 GMT

Hi Manish,

I'm totally strucked here.

Error as field not found:cat_id

as follows my script as

cat_map:

mapping LOAD * INLINE [

cat_id, cat_type

1, clothing

2, clothing

3, clothing

4, clothing

5, footwear

6, footwear

7, clothing

8, clothing

];

LOAD cat_id,

CategoryName as Category,

Description,

ApplyMap('cat_map',cat_id,'unknown') as cat_type;

SQL SELECT *

FROM Categories;

Re: Need your support

maxgro — Thu, 01 May 2014 16:15:35 GMT

replace cat_id (bold)

with the name of the field in your source database

perhaps [Category ID] ?

LOAD cat_id,

CategoryName as Category,

Description,

ApplyMap('cat_map',cat_id,'unknown') as cat_type;

SQL SELECT *

FROM Categories;

Re: Need your support

Greg_Williams — Thu, 01 May 2014 16:18:19 GMT

Is the issue that you are not seeing results or that the applymap is broken?

Suggestions:

ApplyMap('cat_map',cat_id,'unknown') as cat_type; > try this > ApplyMap('cat_map',cat_id) as cat_type;

-Greg

Re: Need your support

MK_QSL — Thu, 01 May 2014 18:20:20 GMT

Error as field not found:cat_id

Means when script is running, it is unable to find the filed named cat_id

There are two places where cat_id is used.

1) in Inline table : There is no possibility of not found here

2) in 2nd table where you are using ApplyMap.... There could be possible as Massimo Grossi replied that here the field name in your SQL server could be something different.

You need to change cat_id with the original name as per your SQL database at two places, as per the suggestion from Massimo Grossi.

Hope this helps...

Update : From you enclosed images, looks like below can solve your problem..

cat_map:

mapping LOAD * INLINE [

cat_id, cat_type

1, clothing

2, clothing

3, clothing

4, clothing

5, footwear

6, footwear

7, clothing

8, clothing

];

LOAD [Category ID],

CategoryName as Category,

Description,

ApplyMap('cat_map',[Category ID],'unknown') as cat_type;

SQL SELECT *

FROM Categories;

Re: Need your support

Greg_Williams — Thu, 01 May 2014 18:45:31 GMT

From this > ApplyMap('cat_map',[Category ID],'unknown') as cat_type;

try

this > ApplyMap('cat_map',CategoryName,'unknown') as cat_type;

OR do this > CategoryName as [Category ID],

-Greg

Re: Need your support

Joseph_Musekura — Thu, 01 May 2014 20:17:52 GMT

Hi,

In your source data you have Category ID and in the mapping you have Cat_id.

When applying 'cat_map'

ApplyMap('cat_map',cat_id,'unknown') as cat_type === > wrong (cat-id is not in your source data)

ApplyMap('cat_map', [Category ID], 'unknown') as cat_type === > CORRECT (example attached)

Re: Need your support

Fri, 02 May 2014 03:05:43 GMT

Hello everyone,

Yeah!!! Got it and thank you VERY much for the quick accurate response.

1).Also by using without Inline statement and by using IF statement we can write as below as

Categories:

LOAD CategoryID,

CategoryName,

Description,

if(CategoryID=5 or CategoryID=6, 'footwear','clothing') as [Category Type];

SQL SELECT *

FROM Categories;

from this also we got same output.But in this scenario which one is the best?When we go for Inline and

when we go for If statement?

2).How can retrieving the data (by using where Title like 'Sales%')as field name as sales person(employees with sales responsibilities) as per BI?

I wrote the script as like this

[Last Name]&' '&[First Name] where Title like 'Sales%' as salesperson;

but getting an error while we are following the same.

Can anyone help me in this.

Re: Need your support

CELAMBARASAN — Fri, 02 May 2014 05:32:46 GMT

1. In my concern both are same, might very less difference on performance.

2. You can use

Load *, [Last Name] & ' ' & [First Name] AS SalesPerson;

Select * FROM Employees Where Title like 'Sales%';

Load *, [Last Name] & ' ' & [First Name] AS SalesPerson

Where Title like 'Sales%';

Select * FROM Employees;

I prefer first one, because it will restrict from the datasource itself but 2nd one loads all data from datasource after that qlikview filters based on the where condition.

Re: Need your support

nizamsha — Fri, 02 May 2014 05:45:45 GMT

In Source Table u have a Field have [Category ID],but u have taken catID have a look at it

cat_map:

mapping LOAD * INLINE [

cat_id, cat_type

1, clothing

2, clothing

3, clothing

4, clothing

5, footwear

6, footwear

7, clothing

8, clothing

];

LOAD [Category ID],

CategoryName as Category,

Description,

ApplyMap('cat_map',[Category ID],'unknown') as cat_type;

SQL SELECT *

FROM Categories;