topic Re: Merge four fields in QlikView

Merge four fields

Fri, 16 Oct 2015 05:04:24 GMT

I'm new to qlikview and I've got this requirement in which I have four fields YTD_AC, YTD_BU, MNT_AC and MNT_BU coming straight from table and I want to merge them for the current year i.e. 2015 and all the months. Refer image for better understanding. How to implement that? Please help!

Re: Merge four fields

Qrishna — Fri, 16 Oct 2015 13:07:15 GMT

I guess we need more info on this.

could you please post a demo document?

Re: Merge four fields

Anonymous — Fri, 16 Oct 2015 13:10:23 GMT

one Expression should do it:

sum( YTD_AC)+sum(YTD_BU)+sum(MNT_AC)+sum(MNT_BU)

Dimension still is yearmonth

Re: Merge four fields

ramasaisaksoft — Fri, 16 Oct 2015 13:10:54 GMT

Hi Harshit,

First u need to create Inline Load for those 4 fileds

LOAD * INLINE [

ACTYTD

BUDMTH

BUDYTD

ACTMTH

];

WHEN U ARE WRITING expression u need to calculate like

if(id='',

sum(),

if(id='',

sum(),....

Re: Merge four fields

Fri, 16 Oct 2015 13:13:06 GMT

when you say merge, do you mean add the fields together?

Re: Merge four fields

Mon, 19 Oct 2015 05:16:08 GMT

Here is the thing......the zone name, kpi name, kpi code, Unit, YTD_AC, YTD_BU, MTH_AC and MTH_BU, Month and year, all these values are coming from table. Now for a selected year and month I've to display the above report format....If I selected only year and not month then it should display the data of all the months for a given year as displayed above....and if I selected both a year and month...it should display data for only that year and month....What should I do..?

Re: Merge four fields

petter — Mon, 19 Oct 2015 15:50:02 GMT

It is typically what you can make with a Pivot Table in QlikView.

You should have a field for:

- Zone Name

- KPI Name

- KPI code

- Unit .... not sure if that is really necessary as a field... is it the % sign here?

- YearMonth

- MetricType: YTD_AC .... etc

First three should be dimensions on the left side as row labels

The last two should be dimensions that you drag up into column labels

Re: Merge four fields

petter — Mon, 19 Oct 2015 16:06:53 GMT

Here is a sample on how to do it:

Re: Merge four fields

Tue, 20 Oct 2015 06:14:17 GMT

Yeah, Peter this is the exact format I need it for my report, how do I implement the YearMonth because year and month are two different dimensions here.

Re: Merge four fields

petter — Wed, 28 Oct 2015 11:57:25 GMT

You either have to create this field in you load script like this:

Year( theDate ) & Num( Month( theDate ) , '00' ) AS YearMonth

you can create a calculated dimension using the same logic - the dimension should be called YearMonth and expression is: Year( theDate ) & Num( Month( theDate ) , '00' )

Here I presume that you have a date field called theDate ... if it is two separate fields already you have:

theYear & Num( theMonth , '00' )

Re: Merge four fields

Mon, 07 Dec 2015 07:29:12 GMT

Hi Peter,

I've now got an added requirement that I've to also display the December data of previous year and the year before that for comparison purposes as asked by client. How do I do that. Please help