topic Re: Combining columns in QlikView

Combining columns

Wed, 21 May 2014 03:51:05 GMT

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

Re: Combining columns

Wed, 21 May 2014 04:07:21 GMT

Can you try using MakeDate function?

Re: Re: Combining columns

Wed, 21 May 2014 04:13:15 GMT

PFA

Re: Re: Combining columns

Wed, 21 May 2014 04:17:20 GMT

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

Re: Combining columns

Wed, 21 May 2014 04:19:51 GMT

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

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

Re: Combining columns

dinuwanbr — Wed, 21 May 2014 04:24:43 GMT

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

Re: Combining columns

Wed, 21 May 2014 04:33:29 GMT

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??

Re: Combining columns

Wed, 21 May 2014 04:34:28 GMT

it would work for sure.

Re: Combining columns

Wed, 21 May 2014 04:35:07 GMT

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

Re: Combining columns

Wed, 21 May 2014 04:43:20 GMT

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??

Re: Combining columns

dinuwanbr — Wed, 21 May 2014 04:54:42 GMT

Hi,

if you want to do other calculations in front end then use that expression in the calculation. Otherwise if you want it in script level load it as a separate field as follows using makedate function.

makedate(dailyYear,dailyMonth,dailyDate) as dailyClosed_date

-dinu'1

Re: Combining columns

Wed, 21 May 2014 05:12:21 GMT

tharidu i did try that in the script bt i am getting an error

this z my code below is t wrong??

dayly:

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,

makedate(daylyDate,daylyMonth,daylyYear) as dClose_date,

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

FROM

[..\Downloads\2014 incidents.xls]

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

Re: Combining columns

its_anandrjs — Wed, 21 May 2014 05:24:36 GMT

Makedate function uses year,month,day in the function see the

eg:-

Makedate(Year,Month,Day)

For your field it is

MakeDate(Right([Closed Date],4) , Mid([Closed Date],5,3) , Mid([Closed Date],9,2)) as dClose_date,

because the fields daylyDate, daylyMonth, daylyYear are not created in the current table it will will be used in another table in any resident table.

Re: Combining columns

Anonymous — Wed, 21 May 2014 05:25:27 GMT

try like

makedate(Mid([Closed Date],9,2),Mid([Closed Date],5,3),Right([Closed Date],4)) as dClose_date,

Re: Combining columns

Wed, 21 May 2014 05:42:14 GMT

Nisha i tried it but it doesn't show any values just an empty field

Re: Combining columns

Wed, 21 May 2014 05:58:35 GMT

i tried it but it doesn't show any values just an empty field

Re: Combining columns

MayilVahanan — Wed, 21 May 2014 06:14:32 GMT

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$]);

Re: Combining columns

Thu, 22 May 2014 06:18:45 GMT

thank you sooo mch it worked just fine...