topic Re:field Not recognised in Qlikview in QlikView

field Not recognised in Qlikview

Tue, 23 Nov 2010 12:04:23 GMT

Hello Guys,

I am confused why it does not work, but when I load the following table from the SQL Databank into Qlikview the category field does not give any fields. I wonder what can be the reason.

SQL Databank view:

Qlikview view:

I tried text(Category) as Category but it does not work. Any Ideas?

Thanks in advance

sravan

field Not recognised in Qlikview

Tue, 23 Nov 2010 12:30:08 GMT

Hello sravan,

for testing I would take a look at the source and concat the database-column with a leading and trailing char of your choice. Something like this (in the SQL-Statement!): '#' || category || '#' as category. Then reload and see whats happening.

HtH

Roland

field Not recognised in Qlikview

Tue, 23 Nov 2010 12:39:02 GMT

Hi Roland,

Thanks for your Answer. I did it and gives this result


Products:

LOAD Id,
 Sequence,
 Name,
 Description,
 '#'& Category & '#' as Category,
 GoodProductYN;
SQL SELECT * from Table;

It does not recognise the field and I dont know why?

field Not recognised in Qlikview

Miguel_Angel_Baeyens — Tue, 23 Nov 2010 12:57:20 GMT

Is that any kind of run-time calculated field (say flowfields in MS Navision, or using some triggers or procedures to get the result) or is a field in a plain table? Is the field called Category? Fields are case sensitive in QlikView, and the same query you do for SQL server may not work in QlikView (as some database field names are case insensitive).

Regards

field Not recognised in Qlikview

Tue, 23 Nov 2010 13:28:17 GMT

Miguel the field is in a plain table and called Category. It is not calculated as it is just a One character field which mentions which category th product is.For ex:

G for Good

S for Scrap

R for Rework

I am not doing any query. I am just viewing the table in SQL and the view shows as above in the Attached figure before. The same field does not have any input in Qlikview.

The Properties of this field are as follows:

Calculalation( Is Berechnet) is false, only 1 char long etc..

Any help?

field Not recognised in Qlikview

Tue, 23 Nov 2010 13:56:03 GMT

Hello sravan,

I did NOT mean the load statement. I ment the SQL - Statement. I saw your field in your database is called "ActivityCategory". So replace the "*" in your SQL-Statement with the list of SQL-columns and for the Category code similar: SELECT ... '# ' || ActivityCategory || '# ' as Category FROM Tables. This is oracle SQL-syntax. For SQL-Server it is: SELECT ... '# ' + ActivityCategory + '# ' as Category FROM Tables. Then you can see what the database delievers. To decide, wether the extract is wrong (your database) or the load (QV).

field Not recognised in Qlikview

Tue, 23 Nov 2010 14:20:48 GMT

Hello Roland,

Can you please write how the Syntax must be. I am getting a syntax error. I tried like this:


LOAD Id,
 Sequence,
 Name,
 Description,
 Category,
 GoodProductYN;
SQL SELECT Id,
 Sequence,
 Name,
 Description,
 GoodProductYN,
 '# ' || ActivityCategory || '# ' as Category
FROM OEE1.dbo."View_Products";

field Not recognised in Qlikview

Tue, 23 Nov 2010 14:36:45 GMT

when I use only sql script and with out load, I get this script error..

field Not recognised in Qlikview

spividori — Tue, 23 Nov 2010 14:53:48 GMT

Hello sravan,

Try the following:


LOAD Id,
 Sequence,
 Name,
 Description,
 text(Category) as Category,
 GoodProductYN;
SQL SELECT Id,
 Sequence,
 Name,
 Description,
 GoodProductYN,
 Category
FROM OEE1.dbo."View_Products";

field Not recognised in Qlikview

Tue, 23 Nov 2010 14:55:29 GMT

Hello Ronald,

This following code I can run


Products:

LOAD Id,
 Sequence,
 Name,
 Description,
 Category,
 GoodProductYN;
SQL SELECT
Id,
Sequence,
Name,
Description,
Category,
GoodProductYN
FROM OEE1.dbo."View_Products";

when I change Category with your code as following I cant run the code.


Products:

LOAD Id,
 Sequence,
 Name,
 Description,
 Category,
 GoodProductYN;
SQL SELECT
Id,
Sequence,
Name,
Description,
//'# ' || Category || '# ' as Category,
GoodProductYN
FROM OEE1.dbo."View_Products";

The Problem I get is like this

Re:field Not recognised in Qlikview

Tue, 23 Nov 2010 14:56:32 GMT

Hello Sandro,

As I previously said, Text does not work..

field Not recognised in Qlikview

Tue, 23 Nov 2010 15:08:11 GMT

Hello,

then choose the second SQL-Statement in my post above. This is the syntax for SQL-Server. I recognized in the error message that you extract from a SQL-Server database. But code it without blanks at the numbersign. Because the '#' should serve as a bracket to see what does really come from the SQL-Server.

field Not recognised in Qlikview

Tue, 23 Nov 2010 15:32:48 GMT

Hi Roland,

Thanks for your answer. I could come one step forward. The second SQL Statement can be run


Products:

LOAD Id,
 Sequence,
 Name,
 Description,
 Category,
 GoodProductYN;
SQL SELECT
Id,
Sequence,
Name,
Description,
'# ' + Category + '# ' as Category,
GoodProductYN
FROM OEE1.dbo."View_Products";

and I get this

Now what does it mean?

Thankyou for your Patience.

field Not recognised in Qlikview

Tue, 23 Nov 2010 15:41:53 GMT

Hi sravan,

this points to that you got some garbage data in your database-field. Let's try it with this

'<' + Category + '>' as Category,
please with no (!) blanks. Then we should be able to see if the garbage is in front or behind your field-value.
I am looking forward to the result (it looks like there is a special char in the field)
RR

field Not recognised in Qlikview

Tue, 23 Nov 2010 15:51:30 GMT

I added as you said without blanks


'<' + Category + '>' as Category,

and now I get

Does that mean the garbage is behind my field? If so how to fix it?

Thanks for the help

field Not recognised in Qlikview

Tue, 23 Nov 2010 16:13:01 GMT

Hi sravan,

yes, behind or in the field itself, perhaps a simple CR (carrige return). Don't know (your data).

Now play "Emil und die Detektive". How long is your field? Do you have a simple SQL-workbench? How does output is shown there ? Can you export (some rows of) your table into a text-file? Use the brackets at the beginnung and end of the field. Check the output. Ask your data-manager for some possible reasons.

field Not recognised in Qlikview

Tue, 23 Nov 2010 16:41:43 GMT

Hi Roland,

Only 1 char long and that is G or S or R etc..I just got a backup file(.bak) from the company with testdata and I restored it by installing Microsoft SQL Server Management Studio Express. I tried to send it as a textfile but did not find any option to do it.

Thanks for your help and patience. I dont know anyway how to solve this problem.

Sravan

field Not recognised in Qlikview

spividori — Tue, 23 Nov 2010 21:10:47 GMT

Hi sravan!

Have you tried?


LOAD Id,
 Sequence,
 Name,
 Description,
 if(text(Category)='G','Good',
  if(text(Category)='S','Scrap',
  if(text(Category)='R','Rework','Null'))) as Category,
 GoodProductYN;
SQL SELECT Id,
 Sequence,
 Name,
 Description,
 GoodProductYN,
 Category
FROM OEE1.dbo."View_Products";

Re:field Not recognised in Qlikview

Wed, 24 Nov 2010 09:25:35 GMT

Hi Sandro,

It gives only Null() in the answer as I expected.

Thanks anyway. May be someone has an idea how to tackle it in the qlikview side. I am parallely asking the data manager for some possible reasons but they insist it is correct in SQL Side.

Sravan

Re:field Not recognised in Qlikview

tanelry — Wed, 24 Nov 2010 14:38:50 GMT

What does sql server return for below query

SELECT
Category,
ascii(Category)
FROM OEE1.dbo."View_Products"

Are there valid ascii numbers for G,R,S (71, 82, 83)?
If yes then try this in QV:

SQL SELECT
Id,
Sequence,
Name,
Description,
GoodProductYN,
char(ascii(Category)) as Category // convert to number, then back to character
FROM OEE1.dbo."View_Products";