Making Pivot from different files with same column

[bhuprakash]

Dear all,

Could you please help me to get the data in below format because when I am loading the data then it is adding the counts at one place but I need it country wise. I have separate file for all countries and there is no column of country in file.

[Anil_Babu_Samineni]

We can make calc. Dimension but not really sure how you want to split each

ValueList('Indonesia', 'Malaysia', 'Philippines', 'Vietnam')

[devarasu07 · Accepted solution]

Hi,

we can build the report we need to excel source file, but u can also try to add country and concatenate them as one fact refer below script.

create pivot table chart,

dimension

[Current State]

Country

(after adding try to enable check box for "always fully expanded" chart property presentation tab and country field u have to transpose Country from row to column )

Measure:

=Count([Lead Code])

Fact:
//IndonesiaDemoFarm:
LOAD 'Indonesia' as Country,
[Lead Code],
    [Assigned To],
    [User ID],
    [Creation Date],
    [Current State]
FROM 
(ooxml, embedded labels, table is [Indonesia DemoFarm]);
Concatenate
//MalaysiaDemoFarm:
LOAD 'Malaysia' as Country,
[Lead Code],
    [Assigned To],
    [User ID],
    [Creation Date],
    [Current State]
FROM

(ooxml, embedded labels, table is [Malaysia Demo Farm]);
Concatenate
//PhilippinesDemofarm:
LOAD Country,
    [Lead Code],
    [Assigned To],
    [User ID],
    [Creation Date],
    [Current State]
FROM

(ooxml, embedded labels, table is [Philippines Demofarm]);
Concatenate
//VietnamDemoFarm:
LOAD
'Vietnam' as Country,
[Lead Code],
    [Assigned To],
    [User ID],
    [Creation Date],
    [Current State]
FROM

(ooxml, embedded labels, table is [Vietnam DemoFarm]);

[bhuprakash]

Thanks a lot Devarasu and Anil. It is working perfectly.

[devarasu07]

Hi,

Close this thread by marking as helpful and correct. thanks

[bhuprakash]

Dear, Only Helpfull is coming in list. Correct Answer is not coming.