Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Merge/Combine multiple fields into 1 demension

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

1 Solution

Accepted Solutions

Re: Re: Re: Re: Merge/Combine multiple fields into 1 demension

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

13 Replies

Re: Merge/Combine multiple fields into 1 demension

Robert

Does the attached give you food for thought ?

Best Regards,     Bill

Not applicable

Re: Merge/Combine multiple fields into 1 demension

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.

Re: Re: Merge/Combine multiple fields into 1 demension

Robert

Just simply delete the bits you don't want.

Revised qvw attached.

Best Regards,    Bill

Not applicable

Re: Merge/Combine multiple fields into 1 demension

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

Re: Merge/Combine multiple fields into 1 demension

Robert

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

Best Regards,     Bill

Not applicable

Re: Merge/Combine multiple fields into 1 demension

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.

Re: Re: Merge/Combine multiple fields into 1 demension

Robert

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

Best Regards,     Bill

Not applicable

Re: Re: Merge/Combine multiple fields into 1 demension

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?

Not applicable

Re: Re: Re: Merge/Combine multiple fields into 1 demension

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!!

Community Browser