topic Re: Problem concatenate field in load-script in QlikView

Problem concatenate field in load-script

kris_vliegen — Wed, 02 Apr 2014 12:36:10 GMT

Hello all,

I have a problem concatenating fields.

I try to make a field wath the Date, an expressien and an artikelnumber like this

DateTimeNum & If(Stock_1<0,8,If(Stock_2<0,7,If(Stock_3<0,6,If(Stock_4<0,5,If(Stock_5<0,4,If(Stock_6<0,3,If(Stock_7<0,2,1))) )))) & ARTNNum as SortDatum

This is the reuslt. Like you can see he always give 00030 as Artikelnumber.

What am I doing wrong?

Regards,

Kris

Re: Problem concatenate field in load-script

its_anandrjs — Wed, 02 Apr 2014 12:47:12 GMT

What is the use of Stock_1.......Stock_7 in the expression

Re: Problem concatenate field in load-script

kris_vliegen — Wed, 02 Apr 2014 12:59:42 GMT

It's a dashboard for fuel-stations.

If you select a Tuesday in the dashboard, Stock1 gives the theoretical stock of Wednesday, Stock 2 of Thursday, ...

I calculate the theoretical stock by the current stock minus the average of the usage of the previous 3 wednesdays, thursdays...

That's how I can see when a tank is out of fuel in a station. This is in the pictures the field expression.

I would like to have a field that contains the DateTimeNum, the expression and the ARTNNum

I would like to implement Google maps and use firstsortedvalue.

That's why I need a field that is numeric .

Re: Problem concatenate field in load-script

marcus_sommer — Wed, 02 Apr 2014 13:13:22 GMT

Is the time-part really important for your calculations? Such structures produced many from unique values which needed a lot of RAM and make the application slow.

I think you should split your timestamp in date + time and try to sort within the resident-load per order by statement:

Load * Resident X Order by date, time, ArtNum;

And if you really need such combined key for sorting create the key rather numeric, perhaps in this kind:

date * pow(10, 7) + ArtNum

- Marcus

Re: Problem concatenate field in load-script

Wed, 02 Apr 2014 13:14:55 GMT

If you check about halfway down your table you can see that the expression does work for a few of the values (which appear to be shorter). Have you tried reducing the specificity (length) of the date?

Re: Problem concatenate field in load-script

marcus_sommer — Wed, 02 Apr 2014 13:25:22 GMT

In addition there are faults within qv by large numbers and/or numbers with many digits - therefore reducing the max. count of number or digits may help.

- Marcus

Re: Problem concatenate field in load-script

Roop — Wed, 02 Apr 2014 13:27:49 GMT

I tried the following and I got similar results to yourself.

I think that it ma be a problem of too many decimal places:

load * inline

[a,b

12/12/12 20:20:14, 01

12/12/12 20:20:14, 02

12/12/12 20:20:14, 03

12/12/12 20:20:14, 04

12/12/12 20:20:14, 05

];

Bee:

Load

date(a) as a,

num(date (a)) & 1 & b as c,

num(date(a)) & ':' & 1 & ':' b as c_new

resident a;

drop table a;

The value in c_new was correctly assigned but not quite what you want but you can probably work with it. The same issue as you suggested was displayed in c.

I hope this helps

Re: Problem concatenate field in load-script

kris_vliegen — Wed, 02 Apr 2014 13:49:21 GMT

Hi all,

I have created a new articlenumber with 1 digit.

UniqueArtTemp:

LOAD

AardcodeID & HoofdgroepID & SubgroepID & FamilieID & Volgnr_Artikel as ARTN

FROM

[..\03 QVD\Demo_PAR.QVD]

(qvd) WHERE(AardcodeID = 'B');

UniqueArt:

Mapping LOAD

distinct(ARTN),

rowno() as ARTNID

Resident UniqueArtTemp order by ARTN;

And I have used the ARTNID in my concatenation. Now is everthing working fine.

The problem was indeed too many numbers after the comma.