topic Re: New rows in QlikView

New rows

Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: New rows

sunny_talwar — Fri, 01 Jul 2016 16:28:26 GMT

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;

Re: New rows

marjan_it — Sat, 02 Jul 2016 08:12:15 GMT

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

];

Re: New rows

qlikview979 — Sat, 02 Jul 2016 10:42:21 GMT

Hi ,

This is help help full to u

Please find this attached file

Regards,

Mahesh

Re: New rows

Mon, 04 Jul 2016 18:49:20 GMT

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

Re: New rows

Mon, 04 Jul 2016 18:50:32 GMT

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

Re: New rows

effinty2112 — Mon, 04 Jul 2016 19:45:50 GMT

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

Re: New rows

qlikview979 — Tue, 05 Jul 2016 05:29:17 GMT

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

Re: New rows

maniram23 — Tue, 05 Jul 2016 05:56:29 GMT

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;