topic Re: Month Year field in QlikView

Month Year field

prma7799 — Fri, 15 Jan 2016 06:33:42 GMT

Hi All,

I have multiple fields like below

SALES UNIT JAN-10

SALES UNIT FEB-10

SALES UNIT MAR-10

SALES UNIT APR-10

I want output like below

Apr-10

May-10

Jun-10

Jul-10

Re: Month Year field

sunny_talwar — Fri, 15 Jan 2016 06:36:35 GMT

Are these field names or values within a field?

If field value then may be this:

Date(Date#(Right(FieldName, 6), 'MMM-YY'), 'MMM-YY')

Re: Month Year field

Anonymous — Fri, 15 Jan 2016 06:40:54 GMT

Hi,

You have to options:

1. You can use this: SubField(<FieldName>,'|',SubStringCount(FieldName,' ')+1

2. You can: RIGHT(<FieldName>, 6)

It will be more beneficial if this was done in the backend script

Re: Month Year field

prma7799 — Fri, 15 Jan 2016 06:43:27 GMT

Hi Sunny,

These are fields.

Total fields 69 every month one field get increase .

Now have Jan-10 to Dec-15.

Thanks

Re: Month Year field

sunny_talwar — Fri, 15 Jan 2016 06:45:12 GMT

Are you using CrossTable Load to make them rows instead of keeping them as Column?

Re: Month Year field

prma7799 — Fri, 15 Jan 2016 06:47:38 GMT

No. I am just fetching data directly.

Yes I will use cross table functionality then use your above solution .

Re: Month Year field

sunny_talwar — Fri, 15 Jan 2016 06:51:33 GMT

May be something along these lines if you are not using CrossTable Load

Table:

LOAD Concat(PurgeChar(@1, '#'), '|', Order) as List;

LOAD @1,

RecNo() as Order

FROM

Community_200222.xlsx

(ooxml, explicit labels, table is Sheet1, filters(

Transpose()

));

LET vList = Chr(39) & Peek('List') & Chr(39);

DROP Table Table;

FinalTable:

LOAD *

FROM

Community_200222.xlsx

(ooxml, no labels, header is 1 lines, table is Sheet1);

For i = 1 to NoOfFields('FinalTable')

LET vFieldOld = '[' & FieldName($(i), 'FinalTable') & ']';

LET vFieldNew = '[' & Date(Date#(Right(SubField($(vList), '|', $(i)), 6), 'MMM-YY'), 'MMM-YY') & ']';

RENAME Field $(vFieldOld) to $(vFieldNew);

NEXT i;

Re: Month Year field

sunny_talwar — Fri, 15 Jan 2016 06:53:08 GMT

Ya if you use CrossTable you can then change those column using the expression provided earlier

Re: Month Year field

sunny_talwar — Fri, 15 Jan 2016 07:01:14 GMT

Script for CrossTable load

Table:

CrossTable(MonthYear, Data)

LOAD ID,

[SALES UNIT JAN-10],

[SALES UNIT FEB-10],

[SALES UNIT MAR-10],

[SALES UNIT APR-10],

[SALES UNIT MAY-10],

[SALES UNIT JUN-10]

FROM

Community_200222_v1.xlsx

(ooxml, embedded labels, table is Sheet1);

FinalTable:

NoConcatenate

LOAD ID,

Date(Date#(Right(MonthYear, 6), 'MMM-YY'), 'MMM-YY') as MonthYear,

Data

Resident Table;

DROP Table Table;

Re: Month Year field

prma7799 — Fri, 15 Jan 2016 07:13:36 GMT

Hi sunindia‌,

I want to implement incremental load here.

Means every month my one month will increase so for that can we use incremental load here?

Thanks

Re: Month Year field

sunny_talwar — Fri, 15 Jan 2016 10:39:20 GMT

If you use CrossTable load you won't have to worry about incrementing a month every month. Which solution are you going with?

Re: Month Year field

prma7799 — Fri, 15 Jan 2016 11:14:41 GMT

Hello sunindia‌,

I am going with Cross table load.

But there is one problem I have big size excel sheet and it has to many column.

PFA ...

In that sales unite and sales values are there and both values are different for every months.

in that i dont want coloumn which is in red color.

Thanks

Re: Month Year field

sunny_talwar — Fri, 15 Jan 2016 11:18:35 GMT

This complicates things little bit. So what happens when you have new data for a month? You insert new column in between to add a new row?

Re: Month Year field

prma7799 — Fri, 15 Jan 2016 11:29:14 GMT

See the attached

First i have to load this excel for Sales Unit in yellow colour means column A to DA

Secondly again i have load this excel for Sales Value in green colour means Column A to GS but exclude column AH to DA which for Sale Unit.

So my question is how to upload this file with cross table load at one time column A to AG is which is in blue same for Sales Unit n Sales Value.

In that I don’t want column DB to DY n GT to HQ which is in Red.

Re: Month Year field

sunny_talwar — Fri, 15 Jan 2016 14:29:17 GMT

Let me spend some time and get back to you

Re: Month Year field

prma7799 — Fri, 29 Jan 2016 06:58:58 GMT

Hi sunindia‌

Any Update on this?

Thanks