Merge/Combine multiple fields into 1 demension

[#1]

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]

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

[Anonymous · Accepted solution]

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

[swuehl]

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]);

[#5]

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