topic Re: SUBFIELD Function Problems in QlikView

SUBFIELD Function Problems

Mon, 24 Feb 2014 14:15:35 GMT

Hello,

The below script runs all right when the application is reloaded with de CTRL+R on the developer IDE, but when scheduled on a trigger and reloaded automatically by the server the column 'equipe_responsavel' returns all values with null instead of the right result, anyone can help me? I've tried to reload only this table, and the result is always the same

[TESTE]:

LOAD

nmequiperesponsavel,

SUBFIELD(nmequiperesponsavel,',') AS 'equipe_responsavel';

SELECT DISTINCT

nmequiperesponsavel

FROM vw_sel_tickets_cw;

Re: SUBFIELD Function Problems

Mon, 24 Feb 2014 14:27:44 GMT

try whit this:

[TESTE]:

NOCONCATENATE LOAD

nmequiperesponsavel,

SUBFIELD(nmequiperesponsavel,',') AS equipe_responsavel;

SQL SELECT DISTINCT

nmequiperesponsavel

FROM vw_sel_tickets_cw;

Hope this helps

Re: SUBFIELD Function Problems

sivarajs — Mon, 24 Feb 2014 14:28:33 GMT

Remove the quotes for field name

[TESTE]:

LOAD

nmequiperesponsavel,

SUBFIELD(nmequiperesponsavel,',') AS equipe_responsavel;

SELECT DISTINCT

nmequiperesponsavel

FROM vw_sel_tickets_cw;

Re: SUBFIELD Function Problems

Mon, 24 Feb 2014 14:37:26 GMT

Doesn't work...

Re: SUBFIELD Function Problems

Mon, 24 Feb 2014 14:38:31 GMT

Doesn't work...

Re: SUBFIELD Function Problems

sivarajs — Mon, 24 Feb 2014 14:44:44 GMT

will nmequiperesponsavel field gets value when reloading from trigger

Re: SUBFIELD Function Problems

jonathandienst — Mon, 24 Feb 2014 14:47:07 GMT

What is the value you expect? This script will return one line for each subfield token (comma separated) from each source row. It will return nulls for empty tokens (two consecutive commas).

If that's not what you require, then you need to be more specific about what you are getting and what you require, preferably by posting some sample data and a qvw model (reduce in size if its large and scramble confidential fields).

HTH

Jonathan

Re: SUBFIELD Function Problems

jfkinspari — Mon, 24 Feb 2014 14:52:05 GMT

Try and debug by taking the script part by part

does SELECT DISTINCT filename FROM ... Work, when executed by Publisher?

Re: SUBFIELD Function Problems

Mon, 24 Feb 2014 14:53:59 GMT

yes!

Re: SUBFIELD Function Problems

christian77 — Mon, 24 Feb 2014 15:04:48 GMT

I tjink you need to specify what part of your field you want.

subfield(Field,'separator',1) that menas 1st part subfield(Field, 'separator', 2) 2nd part.

What happens if some values don't bring separator cause they are just one part?

Lionel - Messi

Cristiano - Ronaldo

Pele

In the 3rd case, it wont give you anything.

Then use subfield(Field & '-', '-', 1) ------> Concat yourself the separator.

Also, place an example of your records to see how they are coming.

Salutes

Re: SUBFIELD Function Problems

Mon, 24 Feb 2014 15:09:23 GMT

This is what i get when i reload:

manual.bmp (attached)

And this is what i get when server reload:

server.bmp (attached)

Is the same script, the same QVW file, there is only this table on the this QVW and olny this little script that i posted.

Re: SUBFIELD Function Problems

Mon, 24 Feb 2014 15:11:35 GMT

Jonathan Dienst & Chris Conejero

Re: SUBFIELD Function Problems

Mon, 24 Feb 2014 15:12:08 GMT

Yes it works!

Re: SUBFIELD Function Problems

Anonymous — Mon, 24 Feb 2014 15:17:08 GMT

Looks like a bug. Maybe related to the special characters.

Try this, with simple characters:

[TESTE]:

LOAD

nmequiperesponsavel,

SUBFIELD(nmequiperesponsavel,',') AS equipe_responsavel;

LOAD * INLINE [

nmequiperesponsavel

"A,B,C"

"E,F,G"

];

Re: SUBFIELD Function Problems

Mon, 24 Feb 2014 16:51:42 GMT

It gets the same problem! Could it be any configuration on my QVW file ?

Re: Re: SUBFIELD Function Problems

Anonymous — Mon, 24 Feb 2014 17:02:50 GMT

I tried this (attached) on Desktop and on QVS (Publisher), works in both cases.
If it works for you in Desktop but not in QVS, something isn't quite right in QVS configuration, but nothing's wrong with this QVW.

Re: SUBFIELD Function Problems

Mon, 24 Feb 2014 17:03:22 GMT

Loading on a tmp table doesn't work too...

SELECT distinct

nmequiperesponsavel

into #tmp_teste

FROM [vw_sel_tickets_cw] ;

[EXPLODE_EQUIPE]:

LOAD DISTINCT

nmequiperesponsavel,

LTRIM(RTRIM(SUBFIELD(nmequiperesponsavel,','))) AS 'equipe_responsavel';

SELECT *

FROM #tmp_teste ;

Re: SUBFIELD Function Problems

Mon, 24 Feb 2014 17:09:41 GMT

Thanks, but it doesn't work too...

Run ok on the Desktop, but not on Server...

I'm using QlikView 9.00, could it be a bug?

Re: SUBFIELD Function Problems

Mon, 24 Feb 2014 17:30:05 GMT

Thanks everyone, but this is taking a lot o my time. I'll build this function on SQL Server or just use SUBFIELD on the chart expresssions instead of load script.

Thanks a lot!

Re: SUBFIELD Function Problems

rwunderlich — Mon, 24 Feb 2014 19:21:34 GMT

Yes, it probably is a bug. There was that exact bug on V9 SR7 -- two parm subfield returns nulls. I suspect you are using SR7 on the server and a different build on the desktop.

-Rob

http://masterssummit.com