Generating data Month and ID wise from above records having value.

[ankitbisht01]

Hi All,

I have a data set having Month, Account num, wise Status . Giving same data set example.

Account Num,   Month,    Status

  C000001     ,  May 2010,     0

  C000001    ,  June 2010,     -

  C000001    ,   July 2010,      -

  C000001    ,   Aug 2010,      0

  C000001    ,   Sep 2010,      2

  C000001    ,   Oct 2010,      -

  C000001    ,   Nov 2010,     -

  C000005     ,  May 2010,     0

  C000005    ,  June 2010,     -

  C000005    ,   July 2010,      -

  C000005    ,   Aug 2010,      0

  C000005    ,   Sep 2010,      2

  C000005   ,   Oct 2010,      -

  C000005    ,   Nov 2010,     - 

I already used code:(but not working)

LOAD

     [Account Num],

     Month,

     Status

     If(Len(Trim(Status)), Status,

     If([Account Num]= peek([Account Num]) and Month = peek(Month) ,peek(Status))) as Status1

FROM

(qvd)

Order by Status, [Account Num], Month;

I want to generate the Above provided values( Account Num wise) in  place of Null value.

Please find the attached Data QVD on which i want to generate these values.

Best Regards

Ankit bisht

[tresesco]

Try like:

  If(Len(Trim(Status)), Status,

    If([Account Num]= peek([Account Num]) and Month = peek(Month) ,peek(Status1))) as Status1

[maxgro]

order by works for a resident load

so first load the qvd without order by, then do a resident load with an order by (and a noconcatenate if you have the same fields)

[ankitbisht01]

Hi Maxgro,

I am using this script on Resident load only, i only generated QVD for community only.

[ankitbisht01]

Hi Trsesco,

I tried this code also but , no luck with it . it still shows the same records as Status field .

[Anil_Babu_Samineni]

Try with null like below

If([Account Num]= peek([Account Num]) and Month = peek(Month) ,peek(Status),null()) as Status1

[ankitbisht01]

Not working Anil

[ankitbisht01]

I think i have to do it with loops, any help on that ???

[Anil_Babu_Samineni]

From above what is the expected output. If you have null value there you want to fill which value. Either before value or what

[HirisH_V7]

Hi,

Can you post your'e required output!

Mentioning which parameters need to be included for creating the status 1 column.

-Hirish