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

[ankitbisht01]

Hi Anil and Hirish ,

All the null values should be filled with the above value , that means if Status is 0 on ( May2010) it will be '0' till it is changed to Status '2'. and till the date it is changes again it will be 2 for each (Account Num).  Desired output is like. (in Bold color are the values need to be generated)

Account Num,   Month,    Status

  C000001     ,  May 2010   ,     0

  C000001    ,  June 2010,     0

  C000001    ,   July 2010,      0

  C000001    ,   Aug 2010,      0

  C000001    ,   Sep 2010,      2

  C000001    ,   Oct 2010,       2

  C000001    ,   Nov 2010,      2

  C000005     ,  May 2010,     0

  C000005    ,  June 2010,     0

  C000005    ,   July 2010,      0

  C000005    ,   Aug 2010,      0

  C000005    ,   Sep 2010,      2

  C000005   ,   Oct 2010,        2

  C000005    ,   Nov 2010,      2

[tresesco]

In that case you have to remove the month equality condition. Try like:

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

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

[Anil_Babu_Samineni]

Ankit, You must use Peek only, This case for me it is working

Load [Account Num], Month,  if([Account Num] = peek('Account Num') and Status = '-',peek('Status'),Status) as Status;

LOAD * Inline [

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,    -

  ];

Output Seems This:

[ankitbisht01]

Hi Anil,

It is working with this inline file, but not working on my data set , which is given in form of QVD above, Please try to do it on above QVD.

[Anonymous · Accepted solution]

Hi ankit,

Try this code with your QVD data.

DATA:
LOAD [Account Num],
     MonthName(Month)    as Month,
     Max(Status) as Status
FROM
[..\Downloads\data1.qvd] (qvd)
GROUP BY [Account Num],
     MonthName(Month);

DATA_NEW:
NoConcatenate
LOAD [Account Num],
     Month,
     Status,
     Peek(Status) as PrevStatus,
     IF([Account Num] = Peek([Account Num]),
         IF(IsNull(Status),Peek(Status1),Status),
         If(isNull(Status),0,Status)) as Status1
RESIDENT DATA
WHERE Not(IsNull([Account Num]))
ORDER BY [Account Num] ASC, Month ASC;

DROP TABLE DATA;

Regards!

[HirisH_V7]

Hi,

May be like this,

Using below script,

Temp:
LOAD
[Account Num],
Date(Month,'DD-MMM-YYYY') as Date,
Num(Month) as MonthNum,
Status
FROM
[data1.qvd](qvd) ;


Table:
NoConcatenate
LOAD
[Account Num],
Date,
MonthNum,
Status,
If(IsNull(Status),Peek(Status1), Status) as Status1
Resident Temp where [Account Num]<>Null() order By [Account Num],MonthNum  ;

Drop table Temp;

Then Populated the missing records ,

Data is ordered by, Monthnum in the above Data.

Is this what you required!

PFA,

Hirish

[sunny_talwar]

Can you share the script that you have been using?

[Anonymous]

Hi Hirish, what happen is you have null value when changes the Account? And if first status is null?

[HirisH_V7]

Hi,

i didn't got you ,Please elaborate!!

-Hirish

[ankitbisht01]

Hi Tersesco,

It is not working , its just giving the same values as  in field "Status".