unpivot for loading and maybe repivot in qlikview?

[stevelord]

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.)

[#2 · Accepted solution]

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

[MK_QSL]

You need to use CrossTable while loading data

and

Pivot Table while working UI side

[stevelord]

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.)