Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Convert a timestamp list box in individual boxes of year month day

Hi to all, 

quick question, 

qvw that takes data from a folder with 2.000+ csv files, all the 2000 files with the same columns.

One of those columns is a timestamp  2020-05-20T00:00:00

The script very simple,  LOAD * from C:\editestfiles\*.csv (txt, utf8 , explicit labels, delimiter is ';', msq);

2 problems:

1.I need to split the time stamp to individuals list boxes , year , month , day 

2. need to overpass the problem of explicit labels since all files don't have headers I need to create them.

 

thanks in advance for the help 

 

 

1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

Hi,

try below , may be it is help

load *,
Year(Date(Date#(SubField(@21,'T',1),'YYYY-MM-DD'),'DD/MM/YYYY')) as myyear,
Month(Date(Date#(SubField(@21,'T',1),'YYYY-MM-DD'),'DD/MM/YYYY')) as mymonth,
day(Date(Date#(SubField(@21,'T',1),'YYYY-MM-DD'),'DD/MM/YYYY')) as myday ;

LOAD *
FROM
TB01_20200703_173947.xlsx

 

or

Year((Timestamp#(@21))) as myyear,
month((Timestamp#(@21))) as mymonth,
day((Timestamp#(@21))) as myday

View solution in original post

6 Replies
Highlighted
Employee
Employee

Do you have a sample of the data ?

Highlighted
Creator II
Creator II

Hi Alexasso,

1) convert your timestamp to date like doing this:

Date(Floor( Date#(DATE_FIELD)),'MM.DD.YYYY hh:mm') as DATE

2) use date functions like

year(DATE)

moth(DATE)

etc.

BR,

Bernd

Highlighted
Contributor
Contributor

there you go , attached the file 

column number 21

Highlighted
Master
Master

for the first problem:

Maye be :

load Year(@2) as year,Month(@2) as Month, Day(@2) as Day,*  from C:\sampleedi\*.csv (txt, utf8 , explicit labels, delimiter is ';', msq);

 

for the second :

can you give an exemple of the source file (csv)? and the expected output ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Highlighted
Contributor
Contributor

not working Taoufiq 

Highlighted
Creator III
Creator III

Hi,

try below , may be it is help

load *,
Year(Date(Date#(SubField(@21,'T',1),'YYYY-MM-DD'),'DD/MM/YYYY')) as myyear,
Month(Date(Date#(SubField(@21,'T',1),'YYYY-MM-DD'),'DD/MM/YYYY')) as mymonth,
day(Date(Date#(SubField(@21,'T',1),'YYYY-MM-DD'),'DD/MM/YYYY')) as myday ;

LOAD *
FROM
TB01_20200703_173947.xlsx

 

or

Year((Timestamp#(@21))) as myyear,
month((Timestamp#(@21))) as mymonth,
day((Timestamp#(@21))) as myday

View solution in original post