topic Re: How to use loaded data under a different field name without loading the data again in QlikView

How to use loaded data under a different field name without loading the data again

Tue, 28 Aug 2012 07:37:11 GMT

Hi,

I struggle to find a correct title...

I have sales in different files : Actuals 2011, Plan (budget) 2012, Actuals 2012, etc.

The load is as follow for all files:

Directory;

CrossTable(Month, Sales2012Plan, 4)

LOAD COUNTRY,

[PRODUCT NAME],

Year,

[Sales source],

Jan,

Feb,

Mar,

Apr,

May,

Jun,

Jul,

Aug,

Sep,

Oct,

Nov,

Dec

FROM

[APAC Sales Report - 2012 Plan.xls]

(biff, embedded labels, table is Database$);

what differs are the parts in red.

I have loaded those files to allow me to do graph 1 (see attachment)

Now as I wanted to do the graph 2, I could not find a way to do it with 2011 months followed by 2012 months.

So I reloaded all the exact same tables with the following changes in blue

for budget data :

Directory;

CrossTable(Monthactual, salesplan, 2)

LOAD COUNTRY,

[PRODUCT NAME],

Jan as [Jan-12],

Feb as [Feb-12],

Mar as [Mar-12],

Apr as [Apr-12],

May as [May-12],

Jun as [Jun-12],

Jul as [Jul-12],

Aug as [Aug-12],

Sep as [Sep-12],

Oct as [Oct-12],

Nov as [Nov-12],

Dec as [Dec-12]

FROM

[APAC Sales Report - 2012 Plan.xls]

(biff, embedded labels, table is Database$, filters(

Remove(Col, Pos(Top, 3)),

Remove(Col, Pos(Top, 3))

));

For actuals:

Directory;

CrossTable(Monthactual, salesactual, 2)

LOAD COUNTRY,

[PRODUCT NAME],

Jan as [Jan-12],

Feb as [Feb-12],

Mar as [Mar-12],

Apr as [Apr-12],

May as [May-12],

Jun as [Jun-12],

Jul as [Jul-12],

Aug as [Aug-12],

Sep as [Sep-12],

Oct as [Oct-12],

Nov as [Nov-12],

Dec as [Dec-12]

FROM

[APAC Sales Report - 2012 LBE.xls]

(biff, embedded labels, table is Database$, filters(

Remove(Col, Pos(Top, 3)),

Remove(Col, Pos(Top, 3))

));

I am convinced that there must be a much simpler way (as I have 12000 lines per table and I am loading now 2x 6 tables with some changes + crosstables) it takes for ever.

Please help.

Thnks

Re: How to use loaded data under a different field name without loading the data again

Tue, 28 Aug 2012 07:52:17 GMT

you don't have to load it twice

moreover ... you shouldn't do it:)

try this way:

load all the tables (3?)

//for budget

CrossTable(Month, budget, 4)

LOAD COUNTRY,

[PRODUCT NAME],

Year,

[Sales source],

Jan,

Feb,

...

Dec

//for 2011

CrossTable(Month, actuals, 4)

LOAD COUNTRY,

[PRODUCT NAME],

Year,

[Sales source],

Jan,

Feb,

...

Dec

//for 2012

CrossTable(Month, actuals, 4)

LOAD COUNTRY,

[PRODUCT NAME],

Year,

[Sales source],

Jan,

Feb,

...

Dec

and modify your chart expression like this:

1st chart

dimension : Month

exp1 : sum(budget)

exp2 : sum(${<Year={2011}>}actuals)

exp3 : sum(${<Year={2012}>}actuals)

2nd chart

calculated dimension : Month&' - '&Year

exp1 : sum(budget)

exp2 : sum(actuals)

Re: How to use loaded data under a different field name without loading the data again

Tue, 28 Aug 2012 08:20:29 GMT

Hi,

thank you for your reply...

I have tried what you suggested (some of it before already) but for whatever reason it does not work for graph 2 and return an error for graph 1 for the exp2 and 3...

I attached the files for you to have a look directly...

Thanks

Re: How to use loaded data under a different field name without loading the data again

Tue, 28 Aug 2012 08:29:23 GMT

As requested :

Re: How to use loaded data under a different field name without loading the data again

Tue, 28 Aug 2012 08:39:46 GMT

check this:

Re: How to use loaded data under a different field name without loading the data again

Tue, 28 Aug 2012 08:44:17 GMT

thanks it is a great start.

There is a problem with the bottom graph which does not order the dates in chronological order...

Re: How to use loaded data under a different field name without loading the data again

Tue, 28 Aug 2012 08:49:03 GMT

when i open this file the sort is ok:/

check if ther is only one condition for sort the dimension

=date(date#(Year&' - '&Month,'YYYY - MMM'))

Re: How to use loaded data under a different field name without loading the data again

Tue, 28 Aug 2012 08:54:24 GMT

I did, I am using QV 9, potential issue there ?

Re: How to use loaded data under a different field name without loading the data again

Tue, 28 Aug 2012 09:05:06 GMT

re-post your file please

Re: How to use loaded data under a different field name without loading the data again

Tue, 28 Aug 2012 09:07:49 GMT

I used yours the v3 you sent ! ah ah ah

Re: How to use loaded data under a different field name without loading the data again

Tue, 28 Aug 2012 09:53:28 GMT

Hi,

I found a solution :

By changing the dimension to :

=date#(Month&'-'&Year,'MMM-YYYY')

and getting a sort by numeric value only,

it gives the right order for the month... Anything else does not seem to work on my QV when it works on yours ???!!!

Any idea why ???

Cheers