topic Re: Re: Re: Merge/Combine multiple fields into 1 demension in QlikView

Merge/Combine multiple fields into 1 demension

Thu, 02 Jan 2014 16:31:55 GMT

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

Re: Merge/Combine multiple fields into 1 demension

Anonymous — Thu, 02 Jan 2014 17:41:16 GMT

Robert

Does the attached give you food for thought ?

Best Regards, Bill

Re: Merge/Combine multiple fields into 1 demension

Thu, 02 Jan 2014 17:55:20 GMT

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

Anonymous — Thu, 02 Jan 2014 17:58:39 GMT

Robert

Just simply delete the bits you don't want.

Revised qvw attached.

Best Regards, Bill

Re: Merge/Combine multiple fields into 1 demension

Thu, 02 Jan 2014 18:55:39 GMT

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

Re: Merge/Combine multiple fields into 1 demension

Anonymous — Thu, 02 Jan 2014 19:23:05 GMT

Robert

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

Best Regards, Bill

Re: Merge/Combine multiple fields into 1 demension

Thu, 02 Jan 2014 19:29:21 GMT

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

Anonymous — Thu, 02 Jan 2014 19:32:36 GMT

Robert

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

Best Regards, Bill

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

Thu, 02 Jan 2014 20:49:27 GMT

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?

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

Thu, 02 Jan 2014 21:14:50 GMT

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

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

Anonymous — Thu, 02 Jan 2014 21:19:29 GMT

Robert

The below from the script does the Cross Table load.

Data :

crosstable ( [Repair Number] , [Repair Type] , [Repairs Performed1] 6 )

load

[Received for Repair] ,

[Item No.],

[S/N],

text( [Issue No.] ) as [Issue No.] ,

Issue,

[Repair Location],

[Repairs Performed1],

[Repairs Performed2],

[Repairs Performed3],

[Repairs Performed4],

[Repairs Performed5]

FROM

[12.18.13 Fuse Scopes Repair Spreadsheet.xlsx]

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

It is a normal load up to column 6 which is [Repairs Performed1],

For the rest of the columns it it puts the field names into the output column [Repair Number] and the value into the output column [Repair Type]. So you end with a load more rows in the output than the input.

I am just about to have look at the qvw you have just posted.

Best Regards, Bill

Best Regards, Bill

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

Anonymous — Thu, 02 Jan 2014 21:36:22 GMT

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

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

swuehl — Thu, 02 Jan 2014 22:20:40 GMT

You can also do the CROSSTABLE LOAD only for the Repairs and a link field, thus creating a second linked table for your Repairs. This has the advantage that you don't duplicate your other field values. And if your excel table uses the same field name Repairs Performed for every column, like in your last file upload, you can use the excel column name to alias accordingly:

Data:

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]

FROM

[12.18.13 Fuse Scopes Repair Spreadsheet.xlsx]

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

Repairs:

CrossTable ( [Repair Number] , [Repair Type],1 )

LOAD

D as [Issue No.],

G as [Repairs Performed 1],

H as [Repairs Performed 2],

I as [Repairs Performed 3],

J as [Repairs Performed 4],

K as [Repairs Performed 5]

FROM

[12.18.13 Fuse Scopes Repair Spreadsheet.xlsx]

(ooxml, no labels, header is 1 lines, table is [QlikView spreadsheet]);

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

Fri, 03 Jan 2014 14:00:10 GMT

Thanks Bill! You are a huge help and couldn't be more thankful. Spot on.