Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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
Great thx for really quick response!
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?
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;
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
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;
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
Great thank you!