Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.

Visualization and Usability

Announcements
Qlik Launches Open Lakehouse and advanced agentic AI experience in Qlik Answers! | LEARN MORE

Who Me Too'd this solution

Gabbar
Specialist
Specialist

Got the data from your link, Thanks for that,
Tried many method but none of those worked, found a method to work around it. 
Order:
First 1000
LOAD
order_id,
customer_id,
order_status,
order_purchase_timestamp,
num(timestamp(floor(timestamp(timestamp#(order_purchase_timestamp,'YYYY-MM-DD hh:mm:ss'),'YYYY-MM-DD hh:mm'),1/(24*60)),'YYYY-MM-DD hh:mm')) as Test,
Left(order_purchase_timestamp,len(order_purchase_timestamp)-3) as CalenderKeyField,
order_approved_at,
order_delivered_carrier_date,
order_delivered_customer_date,
order_estimated_delivery_date
FROM [lib://DataFiles/olist_orders_dataset.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

MinMaxDate:
LOAD
Floor(num(Min(CalenderKeyField))) as MinDate,
floor(num(Max(CalenderKeyField))) as MaxDate
RESIDENT Order;

Let vMinDate = peek('MinDate', 0, 'MinMaxDate');
Let vMaxDate = peek('MaxDate', 0, 'MinMaxDate');
Let vDays = Floor(vMaxDate - vMinDate +1);
DROP TABLE MinMaxDate;

TimeCalendarField:
Load
recno(),
date(($(vMinDate) + (recno()/(24*60))-1),'YYYY-MM-DD hh:mm') AS CalenderKeyField
AutoGenerate ($(vDays)+1)*24*60;

Trying other ways to see if any how the time function can work on this.

IF THIS RESOLVES YOUR ISSUE PLEASE ACCEPT THIS AS AN ANSWER

View solution in original post

Who Me Too'd this solution