topic Re: Akward Source Format in QlikView

Akward Source Format

Tue, 04 Mar 2014 05:17:52 GMT

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

Re: Akward Source Format

Tue, 04 Mar 2014 06:16:58 GMT

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

Re: Akward Source Format

Tue, 04 Mar 2014 06:24:41 GMT

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

Re: Akward Source Format

Tue, 04 Mar 2014 06:29:30 GMT

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

Re: Akward Source Format

Tue, 04 Mar 2014 06:30:06 GMT

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

Regards

Re: Akward Source Format

Tue, 04 Mar 2014 07:39:15 GMT

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!