Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Akward Source Format

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

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: Akward Source Format

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

5 Replies
Not applicable

Re: Akward Source Format

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

Not applicable

Re: Akward Source Format

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

Not applicable

Re: Akward Source Format

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

Not applicable

Re: Akward Source Format

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

Regards

Not applicable

Re: Akward Source Format

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

Community Browser