topic Re: Concat and date format issue: Garbage after expression: '00' in App Development

Concat and date format issue: Garbage after expression: '00'

eikenberg — Fri, 01 Mar 2024 08:19:32 GMT

Hello,

i cannot concate a date for using it in a set analysis. I get the error:

I already trunced the time data with "trunc(belegdatum, 'DD')" to remove the time part but it looks it is added afterwards.

Looks like concat cannot handle "space"!?

How can i slove this?

Re: Concat and date format issue: Garbage after expression: '00'

henrikalmen — Fri, 01 Mar 2024 08:38:32 GMT

This is not set analysis, its a "dollar expansion". What you have written gives this result:

2018-01-02 00:00:00.000002018-0-03 00:00

... and so on. And that gives you an error. At least if it's like that just by itself.

What is it that you are trying to do? And why have you wrapped the concat() function within dollar expansion?

Re: Concat and date format issue: Garbage after expression: '00'

eikenberg — Fri, 01 Mar 2024 08:54:26 GMT

I just wrapped this part in the concat function to see the output.

The set analysis needs a list of values, there are no range or >= and <= possibilities afaik.
So i start there with the concat and run in this error.

I need a list of the last seven days in a diagram and try to figure out the function for it. There are already some examples here in the forum but i cannot get them working so i try to break down the problem and ended by the concat.

Re: Concat and date format issue: Garbage after expression: '00'

henrikalmen — Fri, 01 Mar 2024 09:01:37 GMT

It would help if you provide your full set analysis attempt, but try this then:

chr(39)&$(=concat(distinct BELGDATUM, ','&chr(39))&chr(39)

I wrote this quickly and haven't really checked my syntax, but the principle is that you want the result to look like this:

'2023-01-01 00:00.00','2023-01-02 00:00:00'

That is, values separated with commas and surrounded by apostrophes (chr39).

Re: Concat and date format issue: Garbage after expression: '00'

eikenberg — Fri, 01 Mar 2024 12:55:40 GMT

Sorry, it is driving me mad. I setup a test app with inline data.

Would be nice if you can help me to filter the top right with an set analyse of the last seven days.

I attached the qvf file or here is the load script

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=4;
SET FirstMonthOfYear=1;
SET CollationLocale='de-DE';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan.;Feb.;März;Apr.;Mai;Juni;Juli;Aug.;Sep.;Okt.;Nov.;Dez.';
SET LongMonthNames='Januar;Februar;März;April;Mai;Juni;Juli;August;September;Oktober;November;Dezember';
SET DayNames='Mo.;Di.;Mi.;Do.;Fr.;Sa.;So.';
SET LongDayNames='Montag;Dienstag;Mittwoch;Donnerstag;Freitag;Samstag;Sonntag';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';

Test:
Load * Inline [
BELEGDATUM|NETTOTOTAL
01.03.24 00:00:00.000000|6705
29.02.24 00:00:00.000000|59674
28.02.24 00:00:00.000000|64863
27.02.24 00:00:00.000000|51336
26.02.24 00:00:00.000000|63515
25.02.24 00:00:00.000000|81679
24.02.24 00:00:00.000000|20277
23.02.24 00:00:00.000000|10322
22.02.24 00:00:00.000000|28218
21.02.24 00:00:00.000000|22224
20.02.24 00:00:00.000000|60114
19.02.24 00:00:00.000000|56157
18.02.24 00:00:00.000000|10715
17.02.24 00:00:00.000000|96428
16.02.24 00:00:00.000000|62063
15.02.24 00:00:00.000000|53068
14.02.24 00:00:00.000000|93562
13.02.24 00:00:00.000000|73766
12.02.24 00:00:00.000000|25594
11.02.24 00:00:00.000000|69582
10.02.24 00:00:00.000000|86794
09.02.24 00:00:00.000000|24207
08.02.24 00:00:00.000000|17644
07.02.24 00:00:00.000000|69565
06.02.24 00:00:00.000000|86888
05.02.24 00:00:00.000000|10950
04.02.24 00:00:00.000000|98010
03.02.24 00:00:00.000000|65219
02.02.24 00:00:00.000000|42064
01.02.24 00:00:00.000000|34642
31.01.24 00:00:00.000000|41272
30.01.24 00:00:00.000000|69652
29.01.24 00:00:00.000000|40720
28.01.24 00:00:00.000000|4284
27.01.24 00:00:00.000000|43304
26.01.24 00:00:00.000000|18365
25.01.24 00:00:00.000000|75970
24.01.24 00:00:00.000000|85809
23.01.24 00:00:00.000000|44025
22.01.24 00:00:00.000000|85630
21.01.24 00:00:00.000000|28789
20.01.24 00:00:00.000000|81495
19.01.24 00:00:00.000000|29572
18.01.24 00:00:00.000000|21440
17.01.24 00:00:00.000000|87757
16.01.24 00:00:00.000000|71463
15.01.24 00:00:00.000000|5776
14.01.24 00:00:00.000000|50456
13.01.24 00:00:00.000000|55056
12.01.24 00:00:00.000000|67866
11.01.24 00:00:00.000000|74517
10.01.24 00:00:00.000000|19806
09.01.24 00:00:00.000000|99157
08.01.24 00:00:00.000000|48828
07.01.24 00:00:00.000000|93682
06.01.24 00:00:00.000000|93494
05.01.24 00:00:00.000000|70524
04.01.24 00:00:00.000000|62874
03.01.24 00:00:00.000000|16556
02.01.24 00:00:00.000000|5580
01.01.24 00:00:00.000000|16322
31.12.23 00:00:00.000000|61449
30.12.23 00:00:00.000000|79875
29.12.23 00:00:00.000000|68838
28.12.23 00:00:00.000000|26408
27.12.23 00:00:00.000000|3419
26.12.23 00:00:00.000000|74422
25.12.23 00:00:00.000000|86586
24.12.23 00:00:00.000000|82238
23.12.23 00:00:00.000000|6089
22.12.23 00:00:00.000000|38699
21.12.23 00:00:00.000000|22352
20.12.23 00:00:00.000000|55262
19.12.23 00:00:00.000000|55066
18.12.23 00:00:00.000000|90922
17.12.23 00:00:00.000000|74552
16.12.23 00:00:00.000000|95570
15.12.23 00:00:00.000000|23320
14.12.23 00:00:00.000000|20022
13.12.23 00:00:00.000000|41945
12.12.23 00:00:00.000000|78716
11.12.23 00:00:00.000000|72528
10.12.23 00:00:00.000000|84925
09.12.23 00:00:00.000000|41223
08.12.23 00:00:00.000000|83371
07.12.23 00:00:00.000000|34955
06.12.23 00:00:00.000000|63147
05.12.23 00:00:00.000000|11245
04.12.23 00:00:00.000000|67058
03.12.23 00:00:00.000000|59760
] (delimiter is '|');

Many thanks.

Re: Concat and date format issue: Garbage after expression: '00'

henrikalmen — Fri, 01 Mar 2024 14:10:55 GMT

You should choose a different approach if that's what you want to do. Convert your datestring to an actual date field, and then treat the dates as dates. Much easier to calculate things like "last 7 days". It seems you have no actual timestamps in your data so it could be done like this:

Test:
load date#(left(BELEGDATUM,8), 'DD.MM.YY') as BELEGDATUM, NETTOTOTAL;
Load * Inline [
BELEGDATUM|NETTOTOTAL
01.03.24 00:00:00.000000|6705
29.02.24 00:00:00.000000|59674
28.02.24 00:00:00.000000|64863
] (delimiter is '|');

Re: Concat and date format issue: Garbage after expression: '00'

eikenberg — Mon, 04 Mar 2024 06:27:41 GMT

I loading direkt from oracle (lib connect to). In the database it is an real date.

So i guessed it is an real date field in qlik cause the "date picker" from the bundle is working.

How can i convert an field in already loaded data?

Re: Concat and date format issue: Garbage after expression: '00'

henrikalmen — Mon, 04 Mar 2024 06:42:06 GMT

In the data model view you can check if the field is already interpreted as date. If it is you don’t need the date#() wrapper in my suggestion. Just look at how I did the concatenation with apostrophes.

Re: Concat and date format issue: Garbage after expression: '00'

MayilVahanan — Mon, 04 Mar 2024 06:51:19 GMT

Remove the timestamp from your date field

date(left(BELEGDATUM, 8), 'DD.MM.YY') as BELEGDATUM

So that, in ur front end, you can able to see only date format without any timestamp.

Set Anaysis:

Sum({<BELEGDATUM = {">=$(=Date(Max(BELEGDATUM)-6,'DD.MM.YY'))<=$(=Date(Max(BELEGDATUM),'DD.MM.YY'))"}>}NETTOTOTAL)

it will give last 7 days net total info.

Re: Concat and date format issue: Garbage after expression: '00'

eikenberg — Mon, 04 Mar 2024 08:39:19 GMT

In oracle date is always with time, it is not possible to cut it off. As soon as you convert something to date it has also time.

Your Set Analysis work, thanks.

But i read it is not possible to use >= or <= in set analysis!? That info seems to be outdated.

Re: Concat and date format issue: Garbage after expression: '00'

MayilVahanan — Mon, 04 Mar 2024 08:51:59 GMT

Can you send the link reg. ">=" outdated in set analysis? so that,I can know it..
Its in help.

Set modifiers with dollar-sign expansions | Qlik Sense on Windows Help

Re: Concat and date format issue: Garbage after expression: '00'

eikenberg — Mon, 04 Mar 2024 09:39:21 GMT

I searched the forum for "set analysis greater than" and found this Using Greater Than in Set Analysis - Qlik Community - 353310

Didnt found the example in the help.

Re: Concat and date format issue: Garbage after expression: '00'

MayilVahanan — Mon, 04 Mar 2024 09:52:09 GMT

its very old thread. so ignore that.. now, set analysis is much advance 😀

Re: Concat and date format issue: Garbage after expression: '00'

henrikalmen — Mon, 04 Mar 2024 09:54:45 GMT

@eikenberg wrote:

In oracle date is always with time, it is not possible to cut it off. As soon as you convert something to date it has also time.

It's good practice to remove the timestamp part when loading the data into Qlik, if the timestamp part is unnecessary (and it seems to be only zero-values in your case and therefore probably unnecessary to keep in the data model).

Re: Concat and date format issue: Garbage after expression: '00'

eikenberg — Mon, 04 Mar 2024 10:32:54 GMT

I tried to remove the timestamp part, but it is not working.
Oracle to_date gives always date with time back.

And if i convert it to char qlik does not regocnize it as date anymore, even if its fit the given DateFormat.

SET DateFormat='DD.MM.YYYY';

to_char(BELEGDATUM, 'dd.mm.yyyy') as BELEGDATUM -> "$ascii $text"

Is there a way to convert the date field after the load?

Re: Concat and date format issue: Garbage after expression: '00'

henrikalmen — Mon, 04 Mar 2024 10:41:29 GMT

Convert in qlik script, not in sql. You can for example do it with preceding load, something like this example:

mytable:
LOAD date#(left(BELEGDATUM, 8), 'DD.MM.YY') as BELEGDATUM, otherdata;
SQL select BELEGDATUM, otherdata from sqltable;