Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
Hi Oleg, thanks.I have never used the while load before... can you explain how it works please?
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
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?
Can anyone break this down for me please?
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
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
I wasn't clear how on relationship. See if this is what you are looking for.
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?