topic Re: How to group column names as a new dimension? in QlikView

How to group column names as a new dimension?

josephinetedesc — Mon, 11 Sep 2017 05:46:00 GMT

I have a number of columns in a flat file.

For example: [Financial Year],[Financial Month], Company, Division,

[January Net Amount], [January YTD Amount], [January Bud Amount],

[February Net Amount], [February YTD Amount], [February Bud Amount] etc for every month.

How do I put in a dimension called January which will give me just those 3 January columns, another called February which will give me just those February columns?

Re: How to group column names as a new dimension?

Anil_Babu_Samineni — Mon, 11 Sep 2017 05:50:03 GMT

Some thing like below?

Fact:

LOAD [Financial Year],[Financial Month], Company, Division,

[January Net Amount], [January YTD Amount], [January Bud Amount], 'January' as Flag

From Fact;

Concatenate (Fact)

LOAD [Financial Year],[Financial Month], Company, Division,

[February Net Amount], [February YTD Amount], [February Bud Amount], 'February' as Flag

from Fact;

Concatenate (Fact)

LOAD [Financial Year],[Financial Month], Company, Division,

[March Net Amount], [March YTD Amount], [March Bud Amount], 'February' as Flag

from Fact;

.............

Re: How to group column names as a new dimension?

Anonymous — Mon, 11 Sep 2017 05:56:58 GMT

You can Use Cyclic group or Drill down

Chart Properties --> Dimension --> Edit Group --> New Group -->

Select the Dimension --> OK

New Group will be Created.

Name the Group According to your Requirement.

And Add the Dimension which you want in the Group

Regards

Tahemas Momin

Re: How to group column names as a new dimension?

josephinetedesc — Mon, 11 Sep 2017 06:04:45 GMT

thank you that works - but ...

how do I arrange it that when the Flag January is selected that the 4 tables are selected? I suppose set analysis based on Flag = January?

Re: How to group column names as a new dimension?

shraddha_g — Mon, 11 Sep 2017 06:08:35 GMT

Load [Financial Year],[Financial Month], Company, Division,

[January Net Amount] as [Net Amount],

[January YTD Amount] as [YTD Amount],

[January Bud Amount] as [Bud Amount]

'January' as Month

From Table;

concatenate

Load [Financial Year],[Financial Month], Company, Division,

[February Net Amount] as [Net Amount],

[February YTD Amount] as [YTD Amount],

[February Bud Amount] as [Bud Amount]

'February' as Month

From Table;

You can do this for all 12 Month

In this, For dimension Month , When you select January, all 3 Measures will return data for [January Net Amount], [January YTD Amount], [January Bud Amount].

Re: How to group column names as a new dimension?

Anil_Babu_Samineni — Mon, 11 Sep 2017 06:09:12 GMT

Off course?

May be use some thing like below

Pick(Match(Flag, 'January','February','March',...), Sum({<Flag = {'January'}>}Sales), Sum({<Flag = {'February'}>}Sales), Sum({<Flag = {'March'}>}Sales), ....)

The same way, If Customer / USER needs only for January Sales, You could use something like below

Sum({<Flag = {'January'}>}Sales)

Re: How to group column names as a new dimension?

josephinetedesc — Mon, 11 Sep 2017 06:30:53 GMT

Hi Momin

I am not sure how to use the Drill down group because to make the group 'January' I have to include the 4 columns. So I would need to create a super column called January - not sure how to do that.

Re: How to group column names as a new dimension?

josephinetedesc — Mon, 11 Sep 2017 06:31:19 GMT

Yes this works - thank you

Re: How to group column names as a new dimension?

its_anandrjs — Mon, 11 Sep 2017 06:39:54 GMT

I suggest you have to try this

Fact:

Load

[Financial Year],[Financial Month], Company, Division,

[January Net Amount] as [Net Amt],

[January YTD Amount] as [YTD Amt],

[January Bud Amount] as [Bud Amt]

From Source

Where [Financial Month] = 'January Month Selected Here'; // this is for all January Month for all Financial years

Concatenate(Fact)

Load

[Financial Year],[Financial Month], Company, Division,

[February Net Amount] as Net Amt],

[February YTD Amount] as [YTD Amt],

[February Bud Amount] as [Bud Amt]

From Source

Where [Financial Month] = 'February Month Selected Here'; // this is for all February Month for all Financial years

...... Do this for all months and after this you get for all months data in Fact table.

Re: How to group column names as a new dimension?

its_anandrjs — Mon, 11 Sep 2017 06:42:38 GMT

Also please add a flag field over the table also

Ex:-

Fact:

Load

[Financial Year],[Financial Month], Company, Division,

[January Net Amount] as [Net Amt],

[January YTD Amount] as [YTD Amt],

[January Bud Amount] as [Bud Amt],

'Jan' as MonthSelection

From Source

Where [Financial Month] = 'January Month Selected Here'; // this is for all January Month for all Financial years

Concatenate(Fact)

Load

[Financial Year],[Financial Month], Company, Division,

[February Net Amount] as Net Amt],

[February YTD Amount] as [YTD Amt],

[February Bud Amount] as [Bud Amt],

'Feb' as MonthSelection

From Source

Where [Financial Month] = 'February Month Selected Here'; // this is for all February Month for all Financial years

Re: How to group column names as a new dimension?

Anonymous — Mon, 11 Sep 2017 06:46:15 GMT

when you are using any Chart

In the given SnapShot I have used 5 Column for the group January

So I dont think that you have to create any Super Column

Regards

Tahemas Momin

Re: How to group column names as a new dimension?

josephinetedesc — Mon, 11 Sep 2017 06:53:20 GMT

Thank you for this - what I did was look at the help section for Drilldown but ... it seemed to indicate that I needed to have a pivot chart in order to use a group to select the columns which would make up that group. In which case I would then need to have to 12 expressions?

Re: How to group column names as a new dimension?

josephinetedesc — Mon, 11 Sep 2017 06:55:29 GMT

Hi Anand

This worked but I was wondering why was concatenation necessary?

Re: How to group column names as a new dimension?

prieper — Mon, 11 Sep 2017 07:42:41 GMT

Wouldn't it be easier to work with CROSSTABLE? This may prevent errors, if there are not all monthes in the flatfile.

AllData: CROSSTABLE (Item, Value, 4) LOAD * FROM FlatFile;
AllData_wMonth: LOAD *, SUBFIELD(Item, ' ', 1) AS Month RESIDENT AllData;
DROP TABLE AllData;
Data:
LOAD [Financial Year],[Financial Month], Company, Division, Month, Item     AS [NetAmount] RESIDENT AllData_wMonth WHERE WILDMATCH(Item, '*NET AMOUNT');
JOIN (Data) LOAD [Financial Year],[Financial Month], Company, Division, Month, Item     AS [YTD Amount] RESIDENT AllData_wMonth WHERE WILDMATCH(Item, '*YTD Amount');
JOIN (Data) LOAD [Financial Year],[Financial Month], Company, Division, Month, Item     AS [Bud Amount] RESIDENT AllData_wMonth WHERE WILDMATCH(Item, '*Bud Amount');
DROP TABLE AllData_wMonth;

Peter

Re: How to group column names as a new dimension?

josephinetedesc — Mon, 11 Sep 2017 22:46:06 GMT

Thanks Peter that worked too!

I think the simplest way -(at least I was able to follow it ... except for why concatenation!)

is the way that its.anandrjs does it.

Re: How to group column names as a new dimension?

prieper — Tue, 12 Sep 2017 06:59:44 GMT

With Anand's solution you need to know, which months are listed in the file.

Concatenation is necessary as in the first step all data are filtered, which refer to 'January' with renaming of the fields to more generic names. The same is done with 'February', 'March' etc., which are just written beneath the 'January'-data.

Re: How to group column names as a new dimension?

josephinetedesc — Tue, 12 Sep 2017 22:44:56 GMT

Thank you