topic Re: 1 field in 2 columns Load? in QlikView

1 field in 2 columns Load?

tomcatxx — Wed, 25 Nov 2020 16:16:04 GMT

Hello there,

I have a column of Table like this.

Time
3/12/2015 00:21
12/11/2014 01:12
1/5/2014 00:32
...

I want to load that in 2 columns like this. The loaded Date has a same length.

Date	Time
03/12/2015	00:21
12/11/2014	01:12
01/05/2014	00:32
...	...

Have u a ideal? Thanks.

Re: 1 field in 2 columns Load?

shiveshsingh — Thu, 26 Apr 2018 15:36:23 GMT

Please try this

T:LOAD * INLINE [

Time

3/12/2015 00:21

12/11/2014 01:12

1/5/2014 00:32

];

load SubField(Time,' ',1) as Date,

Right(Time,4) as Time

Resident T;

Re: 1 field in 2 columns Load?

shiveshsingh — Thu, 26 Apr 2018 15:39:06 GMT

OR THIS

T:LOAD * INLINE [

Time

3/12/2015 00:21

12/11/2014 01:12

1/5/2014 00:32

];

load DATE(date#(SubField(Time,' ',1),'DD/MM/YYYY'),'DD/MM/YYYY') as Date,

Right(Time,4) as Time

Resident T;

Re: 1 field in 2 columns Load?

sasiparupudi1 — Thu, 26 Apr 2018 19:20:53 GMT

Date (Floor (Timestamp#(yourDateTime,'DD/MM/YYYY hh:mm'))) As Date,

Time(Frac(Timestamp#(yourDateTime,'DD/MM/YYYY hh:mm'))) As Time

Re: 1 field in 2 columns Load?

vishsaggi — Thu, 26 Apr 2018 19:27:43 GMT

Try this?

DateField:
LOAD *, Timestamp(Timestamp#(DateTimeField, 'MM/DD/YYYY hh:mm'), 'hh:mm') AS TimeStamp,
Date(Date#(DateTimeField, 'MM/DD/YYYY hh:mm'), 'MM/DD/YYYY') AS DateStamp INLINE [
DateTimeField
12/3/2015 01:21
2/6/2014 00:23
3/11/2015 00:18
];

Re: 1 field in 2 columns Load?

sunny_talwar — Thu, 26 Apr 2018 19:36:15 GMT

Always a good idea to add Floor and Frac (like sasiparupudi1‌ added) because if you don't you will see repeating values in a list box for DateStamp and TimeStamp and you will wonder what is wrong. The problem is that Date() and TimeStamp() function are just formatting functions, they will make your field look like date and time, but if the underlying value is a date and time field.. you will see multiple dates repeating because they have different time values... similarly the same will be the case for Time...

Re: 1 field in 2 columns Load?

vishsaggi — Thu, 26 Apr 2018 19:56:14 GMT

Fair points. Got it Sunny. Thanks for mentioning that. I thought Timestamp# and Date# would take care of that. Will they not?

Re: 1 field in 2 columns Load?

sunny_talwar — Thu, 26 Apr 2018 21:04:56 GMT

Nope they won't in this case... the expression will have to be how shiveshsingh‌ did in that case

Re: 1 field in 2 columns Load?

vishsaggi — Thu, 26 Apr 2018 21:07:01 GMT

Got it.

Re: 1 field in 2 columns Load?

tomcatxx — Fri, 27 Apr 2018 10:59:02 GMT

Thank you ver much.

Re: 1 field in 2 columns Load?

tomcatxx — Fri, 27 Apr 2018 11:22:23 GMT

Yes, u are right. If I use Date() or TimeStamp() to get 2 columns, 'Date' and 'Time' looks like only date and time. But value of 'Date' contains also date and time. If u look value of 'Date', u get that.

MinMax:
Load
min(Date) as minDate,
max(Date) as maxDate
Resident Table;

-----------------------------------------------

MinMax:

minDate maxDate

42079,521455158 42072,995261815

So I cant use 'Date' to make a master Calendar. I think that SubField(Time,' ',1) as Date is better.

Re: 1 field in 2 columns Load?

tomcatxx — Fri, 27 Apr 2018 11:24:33 GMT

Thank you. It works.

Re: 1 field in 2 columns Load?

sunny_talwar — Fri, 27 Apr 2018 11:26:49 GMT

I think you can truncate the time component (the decimal) by using Floor() function in your main fact table

Date(Floor(Date)) as Date

Similarly, you can use Frac to remove the Date from your timestamp

Time(Frac(Date)) as Time

Re: 1 field in 2 columns Load?

tomcatxx — Fri, 27 Apr 2018 11:41:18 GMT

Cool, it works.The value is also date format now.

Re: 1 field in 2 columns Load?

sunny_talwar — Fri, 27 Apr 2018 11:43:11 GMT

Awesome