Solved: Re: Merge/Combine multiple fields into 1 demension - Qlik Community

Report Inappropriate Content · ‎2014-01-02

I have a issue management spreadsheet where a specific item can have multiple repair instances. How do I combine multiple fields/columns from a spreadhsheet into one demension.

I'm trying to get a count of the items in the Repairs Performedx fields

Anonymous · ‎2014-01-02

Robert

[EDIT: Now with qvw attached]

I have changed your load script & it now loads ok.

You'll need to change the path to xlsx and the xlsx name though, to suit where you keep it.

Data :

crosstable ( [Repair Number] , [Repair Type] , 9)

LOAD

Year ([Received for Repair]) as received_year ,

Month ([Received for Repair]) as received_Month ,

Week ([Received for Repair]) as received_week ,

[Received for Repair],

[Item No.],

[S/N],

[Issue No.],

Issue,

[Repair Location],

[Repairs Performed],

[Repairs Performed1],

[Repairs Performed2],

[Repairs Performed3],

[Repairs Performed4]

FROM

[12.18.13 Fuse Scopes Repair Spreadsheet (1).xlsx]

(ooxml, embedded labels, table is [QlikView spreadsheet]);

The crosstable() statement was missing and then needed adjusting.

Best Regards, Bill

View solution in original post

Anonymous · ‎2014-01-02

Robert

Does the attached give you food for thought ?

Best Regards, Bill

Report Inappropriate Content · ‎2014-01-02

Thanks Bill. I'm positive my explanation was not clear. What I am looking for is total number by repair. You are close but I do not need Repair Performed Separated by instance but need total of all instances Does that make sense?

Example, How many times does 'adjust angulation' occur regardless of column its reported in.

Anonymous · ‎2014-01-02

Robert

Just simply delete the bits you don't want.

Revised qvw attached.

Best Regards, Bill

Report Inappropriate Content · ‎2014-01-02

That is the counts of the 'Issue' not the 1Repairs Performedx'

Anonymous · ‎2014-01-02

Robert

Are you after a count of the various different distinct values across all 5 Repairs Performedx fields ?

Best Regards, Bill

Report Inappropriate Content · ‎2014-01-02

Yes. How would I combine all 5 into one dimension?

Example:

How many total 'adjust angulation' are there, regardless of where they show up in the columns.

Anonymous · ‎2014-01-02

Robert

In that case you probably need a Cross Table load as per attached.

Best Regards, Bill

Report Inappropriate Content · ‎2014-01-02

Perfect. but how did you do that? I'm trying to translate into my qvw and its bombing out. Did you adjust the spreadsheet to make this work?

Report Inappropriate Content · ‎2014-01-02

Bill,

I've attached my qvw with your objects on the last tab. Could you take a look. If I implement the script code you provided my current objects blow up. Not sure what I am doing wrong. It would be great if I could have your help by 530 as I have a meeting to present this data.

Thank you very much!!