Sorting by date — IDD 6.03

[theboyhope]

I have a field called 'Arrival Date' in Analysis Workbench taken in dd/mm/yyyy format. It's not a proper date object, just a string, so when I try to sort by Arrival Date date you can imagine the screwy results I get.

If I could reformat to yyyy-mm-dd that would be much better. Is there any way I can do that without manually typing in all the new dates?

 

[theboyhope]

Currently thinking of installing Cascade and looping through the DB to make the required changes — then getting rid of Cascade again cause I don't really need it.

I'll post it up if/when it works.

 

[mertner]

In PDI v7.x (and possibly in IDD v6 as well - I don't recall), you can work around the issue if you do a little bit of work.

First load all of your scan files into AW - hardware-only is just fine, so it should be super-fast, particularly if you load .FSF files. Then do an export of the hardware data to CSV format, exporting the asset tag and the "arrival date" columns.

Now close AW and load the CSV file into a suitable editor - I guess Excel might do it, although I would use a good text editor. Then change the contents of the date field to be yyyy-mm-dd using the editor.

Now restart AW, and define a new Analysis Asset Field in the Load Options, using the CSV option. Point it to your edited CSV file, and let AW know which field is the asset tag field.

Wen you now load your data, you will have a new column containing your sortable date field. The CSV feature can of course be used for other things as well, but this should get you going.

I hope this helps you,
Allan

 

[theboyhope]

My development request has finally come through in 7.3.1 so I'll be trying it after the upgrade.

(Just in case you thought I was ungrateful for not posting back. )