Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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
];
Hi ,
This is help help full to u
Please find this attached file
Regards,
Mahesh
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
>
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
>
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
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
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;