Table Merging In Script

[#1]

I'm loading data into my solution as in the below:

// Status table

Status:

Load * Inline

[Category

In Progress

Awaiting Initial Audit Check

Awaiting Start Date

Start Date Arranged - Contract to be issued

Awaiting Signed Contract

Awaiting Final Audit Check

Awaiting Signed Tracking Form

Complete

On Hold / Withdrawn];

// Overview Table

CorporateOverview:

LOAD

          [Business Unit],

          [In Progress],

          [Awaiting Initial Audit Check],

          [Awaiting Start Date],

          [Start Date Arranged - Contract to be issued],

          [Awaiting Signed Contract],

          [Awaiting Final Audit Check],

          [Awaiting Signed Tracking Form],

          [Complete],

          [On Hold / Withdrawn],

          [Total]

FROM $(vSpreadName)  (ooxml, embedded labels, table is 'Some Name'')

where RowNo()<12;

Once I've got the data, is it possible to create a new table with something like the below:

Business Unit      StatusType          Value

1                         In Progress          50

1                         Complete             20

etc

This would give me the axes I need to draw a graph I'm struggling to create at the minute.

Thanks

[giakoum · Accepted solution]

Hope attached example helps!

[#3]

That is absolutely superb, thanks Ioannis! I thought it must've been a simple solution. For those that can't open the spreadsheet, Ioannis' solution is to apply a CrossTable() function.

[#4]

I actually have another question re: the crosstable function. It works perfectly, except for the fact all the rows in the Status column are prefixed with the name of the table. That isn't an issue in your test app, because you've only got one table.

Any ideas?

[giakoum]

Are you using Qualify command somewhere in your script?

A sample application would help.

[#6]

Once again, you've got it. Forgot I had a Qualify statement above the table import, moved the table and I'm in business. Thanks Ioannis.