Convert timestamp into coulmns while loading data

[christianflood]

Hello,

I am very new to qliksense and

I have a date format that looks like 'Fri Nov 27 20:07:38 UTC 2015' how can I extract the different parts into their own fields?

Day, Month, time and year?

Thanks

Christian

[simondachstr · Accepted solution]

Try using the subfield() functionality with space as a delimiter.

e.g.

subfield(Timestamp,' ',1)      AS [Day Name],

subfield(Timestamp,' ',2)      AS [Month Name],

subfield(Timestamp,' ',3)      AS [Time]

etc.

[eduardo_sommer]

Use the function subfield

subfield(FieldName, ' ', 1) as DayName,

subfield(FieldName, ' ', 2) as MonthName,

subfield(subfield(FieldName, ' ', 4), ':', 1) as Hour,

subfield(subfield(FieldName, ' ', 4), ':', 2) as Minute,

subfield(subfield(FieldName, ' ', 4), ':', 3) as Second,

etc

Eduardo

[christianflood]

Great thx for really quick response!

[christianflood]

Im wondering of how I then can concatenate them into one date field,

I have done this so far:

    Date(Date#(subfield(Datum,' ',2),'MMM'),'MMM') as [Datum-Månad],

    Date(Date#(subfield(Datum,' ',3),'DD'),'DD') as [Datum-Dag],

    Date(Date#(subfield(Datum,' ',-1),'YYYY'),'YYYY') as [Datum-År],

Now I would like to have them into one field

And I have this script for that:

Date(Date#(subfield(Datum,' ',-1),'YYYY'),'YYYY') & '-' & Date(Date#(subfield(Datum,' ',2),'MMM'),'MMM') & '-' & Date(Date#(subfield(Datum,' ',3),'DD'),'DD') as [DatumYYYY-MMM-DD]

But it failing while loading?

There is probably a way that is alot easier to achive what I want?

[sunny_talwar]

May be this:

LOAD *,

          Date(MakeDate([Datum-År], [Datum-Månad], [Datum-Dag])) as [DatumYYYY-MMM-DD]

LOAD Month(Date#(subfield(Datum,' ',2),'MMM')) as [Datum-Månad],

    Day(Date#(subfield(Datum,' ',3),'DD')) as [Datum-Dag],

    Year(Date#(subfield(Datum,' ',-1),'YYYY')) as [Datum-År]

FROM Source;

[christianflood]

So if I do a another LOAD in the script I can use my new fields [Datum-Månad] [Datum-År] [Datum-Dag] ?

And when you do LOAD * FROM Source, is that the operation that refreshes so you can use the new Fields?

Sorry, I am 3 days new to QLIKsense

And it didn't give me any result

[sunny_talwar]

So if I do a another LOAD in the script I can use my new fields [Datum-Månad] [Datum-År] [Datum-Dag] ?

Yes, this is called preceding load. The field names created can be used in a preceding load.

And when you do LOAD * FROM Source, is that the operation that refreshes so you can use the new Fields?

LOAD *, <- Add this part of top of your existing table where you create the three new fields

          Date(MakeDate([Datum-År], [Datum-Månad], [Datum-Dag])) as [DatumYYYY-MMM-DD]

LOAD Month(Date#(subfield(Datum,' ',2),'MMM')) as [Datum-Månad], <- This is where your original table will be from your source (excel or any other database)

    Day(Date#(subfield(Datum,' ',3),'DD')) as [Datum-Dag],

    Year(Date#(subfield(Datum,' ',-1),'YYYY')) as [Datum-År]

FROM Source;

[eduardo_sommer]

This is called preceeding load.When you don't specify the source of a load, the script will use the output of the next load (or SQL SELECT). It forms a pipe for the records. You can use the fields generated in one step as input for the next step. The steps run bottom-up

Eduardo

[christianflood]

Great thank you!