field Not recognised in Qlikview

[#1]

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

[#2]

Hello Sandro,

As I previously said, Text does not work..

[#3]

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.

RR

[#4]

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.

[#5 · Accepted solution]

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

[#6]

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

[#7]

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.

RR

[#8]

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

[spividori]

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

[#10]

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

[tanelry]

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