New rows

[#1]

Hello, im new in using QlikView and I don't know how to manage the following problem

I've a table like this:

                  Mai June July August

country A    1       3      2      6

country B    8       1      4       9

country C    4       6      2       8

country D    5       2      8       3

country E    7       9      1       4

and I need the following table

                  Mai June July August

country A    1       3      2       6

country B    8       1      4       9

EUROPE    9       4      6      15 

country C    4      6       2       8

country D    5      2      8        3

country E     7     9       1        4

ASIA          16   17 11 15 

This means: Europe should bei the sum of country A und B and Asia ist the sum of country C, D and E etc.

How can I do this?

[sunny_talwar]

You want to do this in the script or front end?

For script you can try this:

Fact:

LOAD Country,

          Month,

          Value

FROM Source;

Concatenate (Fact)

LOAD 'Europe' as Country,

          Month,

          Sum(Value) as Value

Resident Fact

Where Match(Country, 'Country A', 'Country B')

Group By Month;

Concatenate (Fact)

LOAD 'Asia' as Country,

          Month,

          Sum(Value) as Value

Resident Fact

Where Match(Country, 'Country C', 'Country D', 'Country E')

Group By Month;

[marjan_it]

you can define a type for country as continent and then use Pivot table and add continent and country as dimension!

LOAD * INLINE [

    country, Continent

    country A, ASIA

    country b, ASIA

    country c, EUROPE   

    country d, EUROPE   

];

[qlikview979]

Hi ,

This is help help full to u

Please find this attached file

Regards,

Mahesh

[#5]

Hello Sunny T,

I don't know how to do this.

In my scipt ist no "Value" and no "Month"

It's like this

LOAD Country,

MAY,

JUNE,

JULY,

AUGUST

FROM

(ooxml, embedded labels, table is Sheet1);

What do I have to use for the group by statement?

Am 01.07.2016 um 18:28 schrieb Sunny T:

>

>

Qlik Community

>

[#6]

Thanks,

this works, but how can I manage to display the sum of each continent

and not only the total sum in the pivot.

Am 02.07.2016 um 10:12 schrieb marjan bakhtiari:

>

>

Qlik Community

>

[effinty2112]

Hi Martha,

Try this script:

Countries:

LOAD * INLINE [

Country, Continent

A,Europe

B,Europe

C,Asia

D,Asia

E,Asia

];

CrossData:

LOAD * INLINE [

Country, Mai, June, July, August

A,    1,       3,      2,      6

B,    8,       1,      4,       9

C,    4,       6,      2,       8

D,    5,       2,      8,       3

E,    7,       9,      1,       4

];

Data:

CrossTable(Month,Value) LOAD * Resident CrossData;

Drop Table CrossData;

Now you can create this pivot table:

Continent	Country	Month	Mai	June	July	August
Europe	A		1	3	2	6
	B		8	1	4	9
	Total		9	4	6	15
Asia	C		4	6	2	8
	D		5	2	8	3
	E		7	9	1	4
	Total		16	17	11	15

The expression is Sum(Value) and set the sort order for your 3 dimensions to "Sort Order" in the Sort tab of the pivot table properties.

Cheers

Andrew

[qlikview979]

Hi Martha,

please find the my  Attached file.

T1:

CrossTable(MONTH, VALUE)

LOAD Country,

     MAY,

     JUNE,

     JULY,

     AUGUST

FROM

(ooxml, embedded labels, table is Sheet1);

T2:

LOAD

sum(VALUE) as VALUE,

Country,

MONTH,

'EUROIPE' AS Country1

Resident T1 Where wildMatch(Country,'country A','country B') Group by Country,MONTH;

Concatenate(T2)

LOAD

sum(VALUE) as VALUE,

Country,

MONTH AS MONTH,

'ASIA' AS Country1

Resident T1 Where wildMatch(Country,'country C','country D','country E') Group by Country,MONTH;

DROP Table T1;

Regards

Mahesh

[maniram23]

Hi,

T1:

CROSSTABLE(Month,Value)

LOAD Country,

      MAY,

      JUNE,

      JULY,

      AUGUST

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Fact:

LOAD Country,

          Month,

          Value

RESIDENT T1;

DROP TABLE T1;

Concatenate (Fact)

LOAD 'Europe' as Country,

          Month,

          Sum(Value) as Value

Resident Fact

Where Match(Country, 'Country A', 'Country B')

Group By Month;

Concatenate (Fact)

LOAD 'Asia' as Country,

          Month,

          Sum(Value) as Value

Resident Fact

Where Match(Country, 'Country C', 'Country D', 'Country E')

Group By Month;