How to load Pivot Excel data source in Qliksense

[ashishp]

Hi There 

We want to load Excel data source in Qlik sense. Excel data has pivot data presentation.

Can you please suggest? 

Please find attached Excel Source. 

(Sheet name-LoadV2 )

[ashishp]

Can someone help? is it possible to load this kind of source data? 

[Chintam]

Hi, We can achieve this type of Loading by using CrossTable loading. Here i understand you want to plot each months data into a single Dimension as Month, which will then show case by year and by Month for Region & Country. 

Please find the link as below ,hope this will be useful. 

https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/LoadData/work-with-cross-...

[martinpohl]

try this

Facts:
crosstable (Month, Value,2) load
CR_REGION,
F2 as Country,
[2018Jan],
[2018Feb],
[2018Mar],
[2018Apr],
[2018May],
[2018Jun],
[2018Jul],
[2018Aug],
[2018Sep],
[2018Oct],
[2018Nov],
[2018Dec],
[2019Jan],
[2019Feb],
[2019Mar],
[2019Apr],
[2019May],
[2019Jun],
[2019Jul],
[2019Aug],
[2019Sep],
[2019Oct],
[2019Nov],
[2019Dec],
[2020Jan],
[2020Feb],
[2020Mar],
[2020Apr],
[2020May],
[2020Jun],
[2020Jul],
[2020Aug],
[2020Sep],
[2020Oct],
[2020Nov],
[2020Dec]
FROM
[lib://yourLib/Downloads/Pivot.xlsx]
(ooxml, embedded labels, header is 18 lines, table is [Load V2],
filters(
Replace(1, top, StrCnd(null)))
)
;

Regards

[ashishp]

@martinpohl thank you for your suggestions. 

Its working with your approach. Can you suggest how can we pick Quarter and Year as seperate columns?  

[martinpohl]

add this to your script:

Month_Map:

mapping load * inline [

Jan,1

Feb,2

Mar,3

Apr,4

May,5

Jun,6

Jul,7

Aug,8

Sep,9

Oct,10

Noc,11

Dec,12];

Calendar_temp:

load distinct Month,

makedate(left(Month,4),applymap('Month_Map,mid(Month,5)) as Monthdate

resident Facts;

Calendar:

load

Monthdate,

year(Monthdate) as Year,

Month,

'Q'&ceil(num(month(Monthdate))/3) as Quarter

resident Calendar_temp;

drop table Calendar_temp;

Maybe add additional field in your calendar.

Regards

[ashishp]

Thanks @martinpohl above script is giving error. can you please check? 

[martinpohl]

change this line

makedate(left(Month,4),applymap('Month_Map,mid(Month,5)) as Monthdate

to

makedate(left(Month,4),applymap('Month_Map',mid(Month,5))) as Monthdate

[ashishp]

Hi There 

We want to load Excel data source in Qlik sense. Excel data has pivot data presentation.

Can you please suggest? 

Please find attached Excel Source. 

(Sheet - Expenses

Sheet - Ops Expenses by Month)

[ashishp]

Hi there 

Any updates on this excel uploads?