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

[MrBosch]

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

[JustinDallas · Accepted solution]

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/...

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

[MrBosch]

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) 😉

[vinieme12 · Accepted solution]

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;

[MrBosch]

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

[vinieme12]

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;

[MrBosch]

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