Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
alexasso-09
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
NitinK7
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
Lisa_P
Employee
Employee

Do you have a sample of the data ?

berndjaegle
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

alexasso-09
Contributor
Contributor

there you go , attached the file 

column number 21

Taoufiq_Zarra
Master II
Master II

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") 😉
alexasso-09
Contributor
Contributor

not working Taoufiq 

NitinK7
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