Date: Thu, 28 Mar 2024 06:20:41 -0600 (MDT) Message-ID: <753968274.2355.1711628441908@confluence-external-wiki> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_2354_567242617.1711628441908" ------=_Part_2354_567242617.1711628441908 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
2014-Aug-12
can be converted to 12/08/2014
.=
=20
=20
In the example used here, we have a CSV file with two different date for= mats, and neither of them are recognised by Yellowfin as a standard date fo= rmat. The CSV used can be located here.
Once the CSV file has initially been loaded, following the steps on eith= er the View Builder= CSV Import or Report Builder CSV Import pages, we can see there are two date fields= in the file.
The Date fields, Start Date and End Date, are shown in the file, but Yellowfin has recognised them as Varchar (te= xt) fields, as they were not formatted in the standard date format.
1. Click on the New Function button on= the left of the CSV Data page. This will open a menu displaying all the fu= nctions that can be built into your file.
Note: this = screenshot is from the Report Builder CSV Import, if using the View Builder CSV Import not all of these= options will be available, as they are available in later stages of the st= andard View Builder process instead.
2. Click on the Date Conversion link t= o start the process.
3. From here you will need to select the field that req= uires converting. We will start with the Start Date field.=
4. Click Create.
5. From here, the Date Conversion wind=
ow will be displayed. First, you can provide a name for the converted versi=
on of the field.
Note: this will be a new field, the original unconverted field will =
remain. You may wish to rename the original field as well.
6. Next, you will need to select which Converte= d Data Type you wish the field to be stored as. If the field conta= ins time components and you wish to use them in reporting, select T= imestamp, otherwise select Date.
Last, you will need to define the Date Format the CSV f=
ield is currently stored in. Yellowfin will always convert=
to the format specified in Administration > Configuration > Regional Settings, which is dd/MM/yyyy
by default, what you =
have to do is tell it how to read the original field. In this example, the =
Start Date field is displaying as 3-Jan-09
, w=
hich means it's formatted as dd-MMM-yy
.
7. Enter dd-MMM into the Date = Format field. You will notice that in the preview panel at the bot= tom of the window, Yellowfin has highlighted the 3-Jan com= ponent of the Sample Data, and as such is returning 3/1/1970. What happens here is that Yellowfin converts the parts you specify (in = this case day and month) and populates the rest with components from the da= te: 1/1/1970.
8. Finish entering the date: enter dd-MMM-yy into the Date Format field. Yellowfin has now conve= rted the entire date in the preview panel.
9. Click Save to complete.
You will now have a new field at the end of your CSV table. This will ha= ve an orange band at the top to identify it as a converted field.
In this example, we are going to convert the End Date f=
ield. At the moment this field looks like a date Yellowfin should recognise=
, as it's in our default format of dd/MM/yyyy
=
, but Yellowfin actually needs to read in dates from the format
1. Click the New Function button, sele= ct Date Conversion and choose the End Date field.
2. Click Create to begin.
3. Complete the Name and Conve= rted Date Type as before.
4. If you were not sure how to specify your date format= , i.e. what characters to use in the Date Format field, cl= ick on the :information: symbol in the field.
5. From here you will be shown what character combinati= ons make up what date components. Work out which characters you need and cl= ick Close to return to the previous screen.
6. Enter dd/MM/yy
and cli=
ck Save to complete.