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?
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 .
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
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?
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.
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
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
date(a) as a,
num(date (a)) & 1 & b as c,
num(date(a)) & ':' & 1 & ':' b as c_new
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
I have created a new articlenumber with 1 digit.
AardcodeID & HoofdgroepID & SubgroepID & FamilieID & Volgnr_Artikel as ARTN
(qvd) WHERE(AardcodeID = 'B');
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.