Help with data

[hopkinsc]

Hi, a client has asked for help..

They have an excel spreadsheet with cost centers, expense codes, values and descriptions on (See example attachment) As you can see from the attachment, the Cost Centres and Expense Codes are seperated out into FROM and TO columns.

I need to make these a single column for each.

So for example..

Instead of it being

Cost Center                          Expense Code

FROM       TO                      FROM           TO

101           101                      800                 801

101           101                      802                 899

102           103                      900                 950

I need it to be..

Cost Center                    Expense Codes

101                                     800

101                                     801

101                                     802

101                                     803 (DOWN TO 899)

101                                     899

102                                     900

102                                     901 ETC

Can anyone tell me if there is an easy way to do this please.

[Oleg_Troyansky]

Use the WHILE load. You will have to reload the table twice using WHILE - once to generate Expense Codes and the second time to generate the Cost Centers.

[hopkinsc]

Hi Oleg, thanks.I have never used the while load before... can you explain how it works please?

[tomhoch1]

If I understand you correctly, something like this my work:

Example:

LOAD From,

     To,

     From1,

     To1,

     Value,

     Desc

FROM

Example.xlsx

(ooxml, embedded labels, table is Sheet1);

Let rows = NoOfRows('Example');

for i = 0 to $(rows)-1

    for cc = Peek('From',$(i), 'Example') to Peek('To',$(i), 'Example')

        for ec = Peek('From1',$(i), 'Example') to Peek('To1',$(i), 'Example')

            NewTable:

            Load *

            Inline [CC, EC

                    $(cc), $(ec)];

        next

    next

next

[hopkinsc]

Hi Tomhoch, This seems to do what i want but i don't understand it, can you please break it down for me to help me understand what it does?

[hopkinsc]

Can anyone break this down for me please?

[tomhoch1]

There is probably a more elegant way but let me know if this helps

// Get number of rows from table Example

Let rows = NoOfRows('Example');   

// Loop over each record from Example - Peek command is zero based

for i = 0 to $(rows)-1   

// Get Cost Center Range for each record and loop over it               

    for cc = Peek('From',$(i), 'Example') to Peek('To',$(i), 'Example')

    // Get Expense Code Range for current record and loop over it

        for ec = Peek('From1',$(i), 'Example') to Peek('To1',$(i), 'Example')

            // Create "NewTable" and automatically concatenate the new Cost

            // Center and Expense code values

            NewTable:

            Load *

            Inline [CC, EC

                    $(cc), $(ec)];

        next // Expense Code in range

    next // Cost Center in range

next  // Record from Example table

[hopkinsc]

Hi Tomhoch,

The new inline tables have no links to the original tables.

If i rename the inline tables from CC and EC to From and From 1, this links them but they do not contain the To or To1 values...

So for example,

In the Excel spreadsheet attached, FUEL has Expense Codes from 800 to 801. but When you select FUEL on the attached QVW only 800 is linked in the new inline table created for Expense Codes (From1).

800 and 801 should be white at this point.  801 is appearing in the 'To1' list boxe

Am i missing something?

Thanks

Chris

[tomhoch1]

I wasn't clear how on relationship.  See if this is what you are looking for.

[hopkinsc]

Hi again.

That worked for the test data i sent you. I have just received ACTUAL data from the client (I have been waiting for this since Monday!!).

I have now amended the script to use the actual data but i can not get it working! It must be something i am doing wrong.

If you have some time could you take a look at it to see what i am doing wrong please?