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

[HirisH_V7]

is it the first status value your'e asking about, how can we declare a value which is either (0,2 as provided data).

So i kept it undefined and as null() .

-Hirish

[Anonymous]

Account  Month     Status

C000001 Oct-2016      2

C000002 May-2010     -

With your code, for C000002 will get value 2 on Status1.

Regards!!

[HirisH_V7]

Yup!,

Thank you,

In your script,

  IF([Account Num] = Peek([Account Num]), 

IF(IsNull(Status),Peek(Status1),Status), 

If(isNull(Status),0,Status)) as Status1 

The bold part, How can it be stated as '0' it can be '2' also right.I believe it can be judged by requirement and what he needs to achieve.

-Hirish

[Anonymous]

I have assigned 0 because ankit doesn't want null values on Status1. In bold part, if the status when de account Num has changed is null I write 0 (as default value), is this status is not null I write the status assigned.

You can change 0 by other default status, for example as -1 and describe it with non-status assigned.

Regards!!

[ankitbisht01]

Hi both,

Yes i wanted the status need to be replicated from above month if it is null  each Account Num wise.

Thanx Manuel Script seems to work fine , and  @Harish  same things i was wondering about this condition

IF([Account Num] = Peek([Account Num]),

IF(IsNull(Status),Peek(Status1),Status),

If(isNull(Status),0,Status)) as Status1  (no i dont want my status to give any value except the first given value Account num wise). and by putting Max(Status) as Status --for each month, you removed that null value which is exactly i needed .Can i remove If(isNull(Status),0,Status) condition as no such value exist??

[Anonymous]

Hi Ankit, i am glad about my answer was helpull for you.

If you don't want to set a default status change If(IsNull(Status),0,Status) by Status only.

Regards!

[Anonymous]

Anyway, i think the error is in how you generate the month in the QVD file. I think that you are using Date function to generate it, like Date(Date, 'MMM-YY'). You will see Mar-10, but if you show your date value you can see that it have a lot of values (01/03/2010,31/03/2010,...), that is because I am using max and monthname.

Regards!

[ankitbisht01]

Yes Manuel, i would like to share my script , through which i am generating the missing months and then through you script assigning the value to these month. this is the whole script. Please suggest if any optimization is required.

As i need to make report month on month wise data, therefore have not generated all the dates , instead only generated months . (as i only need the status of a particular Account number on the max date).

Source:

LOAD [Account Num],

  Date,

  date(Date,'MMM-YY') as Month,

     Status

FROM

(biff, embedded labels, table is Sheet1$);

TMP:

load [Account Num]    as [Account Num],

     date(Min(Date))   as min, 

     date(Date(now())) as max

     FROM

(biff, embedded labels, table is Sheet1$)

     Group by  [Account Num];

NoConcatenate   

Concatenate(Source)

Test:  

     Load

     Distinct([Account Num]),

    // min,

    // max,

    // Year,

     Date(addmonths(monthstart(min),IterNo()-1),'MMM-YY') as Month

     Resident TMP

     while addmonths(monthstart(min),IterNo()-1)<=monthstart(max);

     Drop table TMP;

Final:

Load [Account Num],

  //Month,

   Monthname(Month) as Month,

   Max(Status) as Status

Resident Source

GROUP BY [Account Num],MonthName(Month);

DROP Table Source;

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 Final 

WHERE Not(IsNull([Account Num])) 

ORDER BY [Account Num] ASC, Month ASC; 

DROP TABLE Final;

[Anonymous]

Hi Ankit, try this:

Source:

LOAD [Account Num],

  Date,

  date(makedate(year(Date),Month(Date),1),'MMM-YY') as Month, //Here you will have ever the first day of month

  Status

FROM

(biff, embedded labels, table is Sheet1$);

TMP:

load [Account Num]    as [Account Num],

     date(Min(Date))   as min, 

     date(Date(now())) as max

     FROM

(biff, embedded labels, table is Sheet1$)

     Group by  [Account Num];

NoConcatenate   

Concatenate(Source)

Test:  

     Load

     Distinct([Account Num]) as [Account Num],

    // min,

    // max,

    // Year,

     Date(monthname(addmonths(monthstart(min),IterNo()-1)),'MMM-YY') as Month

     Resident TMP

     while addmonths(monthstart(min),IterNo()-1)<=monthstart(max);

     Drop table TMP;

Final:

Load [Account Num],

  //Month,

   Monthname(Month) as Month,

   Max(Status) as Status

Resident Source

GROUP BY [Account Num],MonthName(Month);

DROP Table Source;

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 //you can change this by Status if you don't want a default status

RESIDENT Final 

WHERE Not(IsNull([Account Num])) 

ORDER BY [Account Num] ASC, Month ASC; 

DROP TABLE Final;