topic Re: Use a date/time strip in a SQL-select (like LOAD AS to a new field). in App Development

Use a date/time strip in a SQL-select (like LOAD AS to a new field).

MrBosch — Fri, 07 Oct 2022 16:24:20 GMT

Hi all...

I have been applying some suggestions from this great forum to get this one possible but to no success:

ODBC CONNECT TO [TP-LIVE-Admin]; LOAD Date(Floor(ao_survey_start), 'DD-MM-YYYY') AS ao_survey_start_date, Time(ao_survey_start, 'hh:mm:ss') AS ao_survey_start_time; SQL SELECT `ao_ip_address`, `ao_survey_start`, `ao_survey_submitted` FROM `clienttp`.ao; SQL SELECT `as_ip_address`, `as_survey_start`, `as_survey_submitted` FROM `clienttp`.as;

I know how it works in the LOAD statement, but how to deal with this trimming/shaving whilst in a SQL select (or... can I just extract the date and time with a function in a plain TEXT-object? I read almost all solutions to be in the LOAD script...

Thanks

Re: Use a date/time strip in a SQL-select (like LOAD AS to a new field).

JustinDallas — Sat, 08 Oct 2022 02:28:37 GMT

I'm assuming that the "ao_survey_start" field is coming in as a Timestamp. If that's the case, you should be able to follow the guidance in this post.

https://community.qlik.com/t5/New-to-Qlik-Sense/How-can-I-extract-Date-and-Time-from-Timestamp/td-p/1237957

Date(Floor([Changed Time Stamp])) as Date, Time(Frac([Changed Time Stamp])) as Time

Re: Use a date/time strip in a SQL-select (like LOAD AS to a new field).

MrBosch — Sat, 08 Oct 2022 06:04:04 GMT

Hi Justin Dallas (TX),

Thanks for your reply. I think that I just did what you wrote. The 'difficulty' here has never been with dates or times splitting/extracting when I loaded some XLS file in the past.

The difference: this time the fields are selected via an ODBC SQL Select. So I cannot just hit the 'Select' statement to modify the fields. I found out that you can still place a LOAD before the Select statement, just like normal. That is what you see in my code.

Unfortunately the script does run just fine but all SQL fields become unavailable and the two new 'load AS fields' are created but empty and 'alone' in the table view.

An original ao_survey_start contains for example: 06-10-2022 22:28:12

I think my problem has only to do with the 'how to deal with a sql select' in combination with a LOAD AS to get the date and time extracted.

I will be experimenting and trial-and-erroring some more.

Bosch, Addison (TX) 😉

Re: Use a date/time strip in a SQL-select (like LOAD AS to a new field).

vinieme12 — Mon, 10 Oct 2022 09:48:03 GMT

there are Two "SQL " loads so you need A Preceding LOAD for each SQL LOAD, but even better just Union your results into One SQL Load

ODBC CONNECT TO [TP-LIVE-Admin]; Main: LOAD Date(Floor(ao_survey_start), 'DD-MM-YYYY') AS ao_survey_start_date, Time(ao_survey_start, 'hh:mm:ss') AS ao_survey_start_time; SQL SELECT `ao_ip_address`, `ao_survey_start`, `ao_survey_submitted` FROM `clienttp`.ao UNION SELECT `as_ip_address`, `as_survey_start`, `as_survey_submitted` FROM `clienttp`.as;

Otherwise the load should be as below

ODBC CONNECT TO [TP-LIVE-Admin]; Main: LOAD Date(Floor(ao_survey_start), 'DD-MM-YYYY') AS ao_survey_start_date, Time(ao_survey_start, 'hh:mm:ss') AS ao_survey_start_time; SQL SELECT `ao_ip_address`, `ao_survey_start`, `ao_survey_submitted` FROM `clienttp`.ao; Concatenate(Main) LOAD Date(Floor(ao_survey_start), 'DD-MM-YYYY') AS ao_survey_start_date, Time(ao_survey_start, 'hh:mm:ss') AS ao_survey_start_time; SQL SELECT `as_ip_address`, `as_survey_start`, `as_survey_submitted` FROM `clienttp`.as;

Re: Use a date/time strip in a SQL-select (like LOAD AS to a new field).

MrBosch — Wed, 12 Oct 2022 23:33:31 GMT

Thanks Vineeth Pujari,

I found out that it did do the trick.

One question remains though.

If the SQL SELECT statement has more than only those 2 fields and when I only mention these 2 fields in the LOAD, are all other SQL Select fields being rejected/ignored by default?

Since this was my initial situation before coming to the community:

Load
e AS modified_e ........

SQL SELECT
a,
b,
c,
d,
e,
f............

And I lost all fields other than modified_e

So, what I did was copying the full SQL SELECT fields list and paste them into the LOAD part (where now only the two fields are being modified as you and Justin described).

Load
a,
b,
c,
d,
e AS modified_e ........

SQL SELECT
a,
b,
c,
d,
e ............

And I got it all correctly. I did expect the two 'specialties' in the LOAD would not interfere with the other SQL SELECT 'load', but it did and will do so probaly in the future too.

Is this normal?

Thanks

Alex

Re: Use a date/time strip in a SQL-select (like LOAD AS to a new field).

vinieme12 — Thu, 13 Oct 2022 01:26:42 GMT

Think of Preceding LOAD in Qlik like Nested SQL Statements

example the below nested query will only load columns specified in the outermost select statement

Select tabA.a,tabA.b From ( Select a,b,c,d from SomeTable ) tabA

the Qlik Equivalent is Preceding LOAD where you load from previously loaded result

Simply load all fields using *

example

Load * ; SQL Select a,b,c,d from sometable; OR Load * ,(y+z)/3.14 as newfield ; Load * ,a+b as y ,c+d as z ; SQL Select a,b,c,d from sometable;

Re: Use a date/time strip in a SQL-select (like LOAD AS to a new field).

MrBosch — Thu, 13 Oct 2022 12:44:59 GMT

Thanks. I hope it will help others too some day.