Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Matching the date format of a field imported from csv

Hi all,

I am trying to match the format of 2 date fields 1 from a resident table and the second one from a csv file.

The first date from the resident table is calculated using the date(date#(WeekEnd(now(),3,-2)))) as myDate

Second date comes from the csv file with the following expression.

LOAD date(date#(myDate)) as myDate from $(myPath)$(vSonPlan).csv (txt, delimiter is ',');

When I try joinint the tables I based on the date the join doesn't work because the fields are not formated the same. I get 2 rows for the same date. 1 from the 1st table 2nd from the second.

Could anyone help??

Thanks

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: Matching the date format of a field imported from csv

Sorry guys it was actually correct. It turns out I wasn't using the date() function in part of the if statement. so some of the dates were coming with the wrong format please ignore the question

4 Replies
vaibsonly
Contributor II

Re: Matching the date format of a field imported from csv

hi bhancerli,

                    ur date formats for both date must b varying

                    Use date function n make it perfect

                      Ex: date(myDate),'DD-MM-YY')

                            date(date#(WeekEnd(now(),3,-2)),'DD-MM-YY')

regards,

Vaibhav

MVP
MVP

Re: Matching the date format of a field imported from csv

I think your problem is not the format (you format both times using date(), so format should be your standard date format), but that weekend is returning an underlying numeric value describing the last millisecond of that weekend day. So you probably need to strip off the time part of your resident table date (you can check this by displaying the numeric value: num(myDate) ).

date(daystart(WeekEnd(now(),3,-2))) as myDate

(You don't need to apply date#() function on Weekend(), because it's already returning a timestamp).

Not applicable

Re: Matching the date format of a field imported from csv

Sorry guys it was actually correct. It turns out I wasn't using the date() function in part of the if statement. so some of the dates were coming with the wrong format please ignore the question

MVP
MVP

Re: Matching the date format of a field imported from csv

Ok, no problem. Just take care that your numeric values match if you want to link these fields as key fields (i.e. if using weekend / yearend, take care of the internal numeric representation when linking to pure date fields).