Qlik Community

New to Qlik Community

Discussion board for questions on how to use Qlik Community and its features.

Announcements
This forum is for questions and information about how to use the Qlik Community.
Please do not post product related questions here.
Select the correct forum from: Qlik Product Forums
Highlighted
sahajaqlik
Not applicable

need help in datamodeling

Hi all,

I have one table of change order which is in epoch format,

Load * inline

CRNO,Starttime,Endtime,CI

1,1538352000,1538353000,Keyboad

2,1538352000,1538353000,Mouse

3,1538352000,1538353000,laptop

 

Now my requirement is for CRNO i need the daterange in minutes to calculate

 

2 Replies
thiago_justen
Not applicable

Re: need help in datamodeling

Try this out:

 

Epoch:
Load * inline [
CRNO,Starttime,Endtime,CI
1,1538352000,1538353000,Keyboad
2,1538352000,1538353000,Mouse
3,1538352000,1538353000,laptop
];

Human_Time:
Load
CRNO,
Endtime as Epoch,
Timestamp(RangeSum('01/01/1970',Starttime/86400)) as Starttime,
Timestamp(RangeSum('01/01/1970',Endtime/86400)) as Endtime,
CI
Resident Epoch;

Drop Table Epoch;

 

Above I'm converting Epoch to Timestamp. Epoch/86400 results in number of days. I did use 01/01/1970 considering the epoch definition (The Unix epoch (or Unix time or POSIX time or Unix timestamp) is the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT), not counting leap seconds (in ISO 8601: 1970-01-01T00:00:00Z). Literally speaking the epoch is Unix time 0 (midnight 1/1/1970), but 'epoch' is often used as a synonym for 'Unix time'. Many Unix systems store epoch dates as a signed 32-bit integer, which might cause problems on January 19, 2038 (known as the Year 2038 problem or Y2038). ).

 

Cheers

 

 

 

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
marcus_malinow
Not applicable

Re: need help in datamodeling

Try this:

LOAD
CRNO,
Timestamp((Starttime/86400)+25569) as Starttime,
Timestamp((Endtime/86400)+25569) as Endtime,
(
Endtime - Starttime) / 60 as DurationInMinutes,
CI
;
Load * inline [
CRNO,Starttime,Endtime,CI
1,1538352000,1538353000,Keyboad
2,1538352000,1538353000,Mouse
3,1538352000,1538353000,laptop
]
;

Community Browser