Solved: Combining columns - Qlik Community

Report Inappropriate Content · ‎2014-05-20

hi

i am trying to combine 3 columns which i have broken down (or extracted ) from another column but it is not working for me

this image shows how i have broken it down and below is the code i used

[Closed Date] ,

Right([Closed Date],4) as daylyYear,

Mid([Closed Date],5,3) as daylyMonth,

Mid([Closed Date],9,2) as daylyDate,

daylyDate & ' ' & daylyMonth <--- i tried combining like this but did not work.. i want to show the date as 01/05/2014 after combining .

would greatly appreciate some help

thank you in advance

MayilVahanan · ‎2014-05-21

Hi

Try like this.

You can achieve like this method also:

Map1:

Mapping Load * Inline

[

Month, MonthNum

Jan , 1

Feb, 2

Mar, 3

Apr, 4

];

Load *, date#(daylyDate&'/'& Date(Date#(ApplyMap('Map1',daylyMonth),'MM'),'MM') &'/'&daylyYear,'DD/MM/YYYY') as dClose_date;

LOAD [Incident ID*+] as daylyIncident_ID,

[Submit Date] as daylySubmit_Date,

[Last Name+] as daylyLast_Name,

[First Name+] as daylyFirst_Name,

[Summary*] as daylySummary,

[Status*]as daylyStatus,

[Assigned Group*+] as daylyAssigned_Group,

[Assignee+] as daylyAssignee,

[Customer Site]as daylyCustomer_Site,

[Closed Date] as daylyClosed_Date,

[Last Modified By] as daylyLast_Modified_By,

Right([Closed Date],4) as daylyYear,

Mid([Closed Date],5,3) as daylyMonth,

Mid([Closed Date],9,2) as daylyDate,

[First Name+] & ' ' & [Last Name+] As daylyName

FROM

[..\Downloads\2014 incidents.xls]

(biff, embedded labels, table is [Sheet 1$]);

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

Report Inappropriate Content · ‎2014-05-21

Can you try using MakeDate function?

Report Inappropriate Content · ‎2014-05-21

PFA

@

Report Inappropriate Content · ‎2014-05-21

i have a problem viewing this cz m using an personal edition of qlikview can u send me a screen shot if u don't mind

Report Inappropriate Content · ‎2014-05-21

Create a list box based on an expression. And type in this expression.

= daylydate & '/' & date(date#(daylymonth,'MMM'),'MM') & '/' & daylyyear

dinuwanbr · ‎2014-05-21

Hi,

Hope you want to create a new list box. If it is so create a new list box in general tab go to field and select <Expression> then edit the expression as follows.

=if(dailyYear>0,dailyYear)&'-'&if(dailyMonth>0,dailyMonth)&'-'&if(dailyDate>0,dailyDate).

-dinu1

Report Inappropriate Content · ‎2014-05-21

this worked fine... but the thing is what i want to do with this column is create charts n do some calculations.. will it work like that??

Report Inappropriate Content · ‎2014-05-21

it would work for sure.

Report Inappropriate Content · ‎2014-05-21

if your question is resolved can you mark this thread as 'Answered'?

Report Inappropriate Content · ‎2014-05-21

Tharidu what u said is working in the list box.. but i run this in the script and make it a separate field so i can use it to do my other calculations??

Combining columns

Related Topics