topic Re: One more field from existed field in QlikView

One more field from existed field

Fri, 16 Oct 2015 06:16:19 GMT

Hi All,

can any one help me on the below scenario,

I have a table with ID, Firm ,

Year, Period,Objective GLObject ,Sales, now I want a table with current year sales and Last year Sales in the same table , how to create these two fields in the same table. any help can be appreciated.

Please find the Sample data.

Regards

John

Re: One more field from existed field

Anonymous — Fri, 16 Oct 2015 06:38:16 GMT

Hi John,

Maybe try:

Data:

LOAD ID,

Firm,

Year,

Period,

Objective,

GLObject ,

Sales

FROM ...;

MaxYear:

LOAD Max(Year) as maxYear

Resident Data;

LET vMaxYear = Peek('maxYear');

Left Join(Data)

LOAD ID,

Sales as CurrentYearSales

Resident Data

Where Year = $(vMaxYear);

Left Join(Data)

LOAD ID,

Sales as LastYearSales

Resident Data

Where Year = ($(vMaxYear) - 1);

DROP TABLE MaxYear;

Re: One more field from existed field

sunny_talwar — Fri, 16 Oct 2015 06:40:25 GMT

Looking for a front end or back end solution?

Re: One more field from existed field

Fri, 16 Oct 2015 06:46:38 GMT

Back End Solution

Re: One more field from existed field

Fri, 16 Oct 2015 06:47:38 GMT

Thanks Sunny Talwar, I wanted to calculate an extra field in the table at back end.

Re: One more field from existed field

sunny_talwar — Fri, 16 Oct 2015 06:50:41 GMT

I think solution provided by neetha P‌ should work in that case

Re: One more field from existed field

Fri, 16 Oct 2015 06:52:57 GMT

Thanks Neetha,

Here ID is not a unique value it has duplicate records , will this logic works ? because I have Period as filter.

Left Join(Data)

LOAD ID,

Sales as CurrentYearSales

Resident Data

Where Year = $(vMaxYear);

Re: One more field from existed field

sunny_talwar — Fri, 16 Oct 2015 06:58:33 GMT

May use add all the fields (if the combination of all the fields make a line unique)

Left Join (Data)

LOAD ID,

Firm,

Year,

Period,

Objective,

GLObject ,

Sales as CurrentYearSales

Resident Data

Where Year = $(vMaxYear);

Re: One more field from existed field

Fri, 16 Oct 2015 07:00:28 GMT

No Luck Sunny

Re: One more field from existed field

Anonymous — Fri, 16 Oct 2015 07:01:06 GMT

create composite key for making unique value and use it.

Re: One more field from existed field

Fri, 16 Oct 2015 07:09:40 GMT

Yes Neetha i did it,however when come to the below step

Left Join(Data)

LOAD Unique ID,

Sales as LastYearSales

Resident Data

Where Year = ($(vMaxYear) - 1);

QV will read the last year Unique values it will not read the Current year values in that case last year values will not populate for the current year selection. How do I over come this.

Re: One more field from existed field

Anonymous — Fri, 16 Oct 2015 07:12:14 GMT

May be try:

Left Join(Data)

LOAD ID,

Sales as LastYearSales

Resident Data

Where Year >= ($(vMaxYear) - 1);

Re: One more field from existed field

sunny_talwar — Fri, 16 Oct 2015 07:15:02 GMT

Try this:

TABLE:

LOAD ID,

Firm,

Objective,

Year,

Period,

[GL Object],

Sales

FROM

community6.xlsx

(ooxml, embedded labels, table is Sheet1);

Left Join (TABLE)

LOAD 'FY' & Max(Right(Year, 2)) as Year,

1 as Flag1

Resident TABLE;

Left Join (TABLE)

LOAD 'FY' & Max(Right(Year, 2), 2) as Year,

1 as Flag2

Resident TABLE;

FinalTable:

LOAD *,

If(Flag1 = 1, Sales) as CurrentYearSales,

If(Flag2 = 1, Sales) as PreviousYearSales

Resident TABLE;

DROP Table TABLE;

Re: One more field from existed field

Fri, 16 Oct 2015 07:42:17 GMT

Thanks Sunny, for your efforts ,however I am expecting output like

in the image you have added the Previous year sales for the FY14 is null but it should not be like that we have sales for the Previous year FY13 those values has to come beside the Current year sales.

Hope you get it.

Regards

John

Re: One more field from existed field

Anonymous — Sat, 17 Oct 2015 11:43:55 GMT

hi john,

sunny's solution reflects your requirement.

try to remove year field in chart in UI and you will get same output as your requirement

Re: One more field from existed field

Sat, 17 Oct 2015 11:48:55 GMT

Thanks Neetha , I tried, however it is not working.

you can find same sample data with Expected result. in the attachment.

Regards

John