topic Re: Multiple date lines where loading "Date and Hour" feild in App Development

Multiple date lines where loading "Date and Hour" feild

Ishragil — Fri, 27 Nov 2020 13:48:22 GMT

I'm trying to load data about mushrooms picking from a table where the "Date" field, mentions date+hour of picking.

I've created a new Dimension for date, using:

DATE(MAKEDATE(1988) + UDATE/1440,'DD/MM/YYYY') as Date,

But the output us several lines for each date (the same amount of lines as if I leave the hour)

How can I consolidate all the lines for certain date to only one line?

Thanks a lot in advance

Re: Multiple date lines where loading "Date and Hour" feild

lironbaram — Fri, 27 Nov 2020 13:56:15 GMT

hi
if you want to convert seconds to date use this

DATE(MAKEDATE(1988) + UDATE/86400,'DD/MM/YYYY') as Date,

Re: Multiple date lines where loading "Date and Hour" feild

Ishragil — Sat, 28 Nov 2020 09:54:29 GMT

Thanks for your prompt reply.

I tried this but it converted all the dates to the year 1988 and left all the multiple lines

Re: Multiple date lines where loading "Date and Hour" feild

PrashantSangle — Sat, 28 Nov 2020 12:58:08 GMT

can you provide sample data with output?

Re: Multiple date lines where loading "Date and Hour" feild

Ishragil — Sun, 29 Nov 2020 16:09:29 GMT

Sure

Here is an example (Please ignore the Hebrew):

This is the data in the Priority (the source) with the date and the hour:

And here is the date from the Qlik:

The same 12 lines and the same total

I would like to consolidate them by the date to only one line per date

Re: Multiple date lines where loading "Date and Hour" feild

Ishragil — Sun, 29 Nov 2020 16:11:19 GMT

In the first snapshot there only 10 lines appears out of the 12 lines

Re: Multiple date lines where loading "Date and Hour" feild

Kushal_Chawda — Sun, 29 Nov 2020 16:26:53 GMT

@Ishragil did you try below

timestamp(MAKEDATE(1988) + UDATE/1440) as Date,

Re: Multiple date lines where loading "Date and Hour" feild

Ishragil — Sun, 29 Nov 2020 17:00:21 GMT

Tried that, but still the same output:

Re: Multiple date lines where loading "Date and Hour" feild

rubenmarin — Sun, 29 Nov 2020 18:27:56 GMT

Hi, taht's beacuse each date is a different value because each one has a different time value, if want to keep only one value for each date you can use floor() to remove the time:

DATE(Floor(MAKEDATE(1988) + UDATE/1440),'DD/MM/YYYY')

Re: Multiple date lines where loading "Date and Hour" feild

Ishragil — Sun, 29 Nov 2020 18:57:30 GMT

I did that:

and the result is still multiple lines, but some lines are missing (in the source there are 12 lines with 1163 Boxes):

Re: Multiple date lines where loading "Date and Hour" feild

Kushal_Chawda — Sun, 29 Nov 2020 21:55:39 GMT

@Ishragil what kind of values stored in UPDATE field ? What is expected output?

Re: Multiple date lines where loading "Date and Hour" feild

PrashantSangle — Mon, 30 Nov 2020 08:15:49 GMT

for debugging purpose,

can you add below line in script then run script , add num_Date in chart and share the output.

Num(Floor(MAKEDATE(1988) + UDATE/1440)) as num_date

I just want to check what value it is evaluating?

Regards,

Prashant Sangle

Re: Multiple date lines where loading "Date and Hour" feild

Ishragil — Mon, 30 Nov 2020 10:14:13 GMT

It gives the same value for all the lines on the same date

Re: Multiple date lines where loading "Date and Hour" feild

Ishragil — Mon, 30 Nov 2020 10:23:27 GMT

Re: Multiple date lines where loading "Date and Hour" feild

Ishragil — Wed, 09 Dec 2020 11:42:48 GMT

I am happy to update that I found the solution from a friend.

the solution is by using the function Datestart(), or to be more specific:

DATE(MAKEDATE(1988) + daystart(UDATE/1440),'DD/MM/YYYY') as Date,