Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi @Dolly123
You seem to have more problems in your data than just the order_date field, but to deal with that first.
When you sent the data to Excel from Qlik, it changed the format, giving a false impression of what you had.
I notice from the load script that you are loading from many identical QVDs, you can make that easier to manage by putting a loop in place. If you replace the entire load script with this it should put you in a better place:
for each vFile in FileList('lib://order_sub_1 (qliksense_qlikadmin)/order_and_subscription_info_*.qvd')
order_and_subscription_info:
LOAD
order_no,
city,
subscription_id,
transaction_id,
order_date as order_date_original,
Date(rangemin(
order_date,
Date#(order_date, 'MMMM DD, YYYY'),
date#(replace(replace(replace(replace(replace(trim(order_date), 'nd ', ' '), 'st ', ' '), 'rd ', ' '), 'th ', ' '), ',', ''), 'DD MMM YYYY'),
date#(keepchar(order_date, '0123456789/'), 'DD/MM/YYYY')
), 'DD/MM/YYYY') as order_date,
order_total,
shipped_date,
delivery_date,
is_completed,
"time",
product_price,
delivery_charge_total,
subscription_no,
order_id
FROM [$(vFile)] (qvd);
next
Ideally the date formats should be sorted on the way into the QVD, rather than on the way out. You have the following differing formats:
Plus a couple of other edge cases. The rangemin statement tries converting each in turn and returns the first to work. If you get any other date formats appear you can add these in also.
It looks like the data may have come from CSVs in the first place, and where there are entries where the columns don't line up correctly. Without fixing this you are going to get some very spurious output.
I hope that getting the date fixed up moves you forward a bit though.
Steve
