Akward Source Format

[#1]

Hi Everyone

I need your help please.  I have a source document that is formatted as per the attached sample.  I need to get it "flattened" i.e. client needs to go next to fund and the total and blank rows need to be dropped.  I've managed to drop the totals and blank rows but I cannot get the client name to insert next to the fund name.

Any help would be appreciated.

Thanks

[#2]

While loading it into QV desktop, did you try using "Enable Transformation Step". That will make your stuff more easy.

[#3 · Accepted solution]

Hi

You can use this script:

// step 1 - fill client column

T1:

LOAD

     @1 as Investment,

     @2 as Account,

     @3 as DateOpened,

     @4 as AnnualFee,

     @5 as LastDistribution,

     @6 as MarketValue,

     if(len(trim(@2))=0,

      @1,

      Peek('Client')) as Client //fill client column

FROM

[QV Discussions.xls]

(biff, no labels, header is 12 lines, table is Worksheet$) //remove header table

where

  WildMatch(@1, '*Total ZAR Portfolio', 'TOTAL ZAR MARKET VALUE')=0 //remove total rows

  and len(trim(@1))>0; //remove empty rows

//step2 remove client from Investment column

T2:

NoConcatenate LOAD * Resident T1 where len(trim(Account))>0;

DROP Table T1;

Table [T2] - is final result

[#4]

Hi Angad

Yes I've tried numerous times and normally I manage to get it right, but this particular one I cannot figure out.  Will try Fedor's solution shortly.

Regards

[#5]

Thanks Fedor - I will try this within the hour and let you know.

Regards

[#6]

Hi Fedor

Thanks - your solution work perfectly with some minor adjustments to cater for a few other wildmatches that must be removed.

Thanks for the help!

PR