topic Re: unpivot for loading and maybe repivot in qlikview? in QlikView

unpivot for loading and maybe repivot in qlikview?

stevelord — Tue, 29 Apr 2014 21:22:31 GMT

Hi, I have this odd table where the sales rep names are column headers and the list of clients are running down column A. I want the sales rep names to just be values in a sales rep field because they can come and go and I don't want to have to add/drop 'fields' every time they do so.

Excel files look like this:

column a column b column c column d

blank header blankcsrep john doe jane doe

clientabc 1 3

client123 2 3

I wouldn't mind making a pivot table to look like that in qlikview, but I'd want it to be a vertical sales rep field that I flip to horizontal after the fact. (And those numbers are something I might want as a 'TicketCount' field or something.)

So instead of loading:

@1 as Client

@2 as blanksalesrep

[john doe]

[jane doe]

...

I'd load:

@1 as Client,

SalesRep,

TicketCount

Ideas? Thanks! (I'll be experimenting with transofrmation steps and stuff in the interim, but there are alot of these files to aggregate and they will be rolling in daily.)

Re: unpivot for loading and maybe repivot in qlikview?

Tue, 29 Apr 2014 23:26:13 GMT

crossteble() is good for this.

You will find syntax it in help and also if you read table file with wizard,you have button for that in 3'rd step.

regards

Darek

Re: unpivot for loading and maybe repivot in qlikview?

MK_QSL — Wed, 30 Apr 2014 06:05:26 GMT

You need to use CrossTable while loading data

and

Pivot Table while working UI side

Re: unpivot for loading and maybe repivot in qlikview?

stevelord — Fri, 02 May 2014 22:40:16 GMT

Thanks! Sweet and simple, just one of those functions I hadn't had to use yet. (Nice to have something that seemed like it should be hard be simple, instead of the usual should be simple but turns out near impossible.)