Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor

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

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

Highlighted
MVP
MVP

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

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

Highlighted

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

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:

Capture.PNG

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Contributor

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

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.

Highlighted
Valued Contributor III

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

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!

View solution in original post

Highlighted
Honored Contributor

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

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 ,

Peek From two columns comm-237255.PNG

Data is ordered by, Monthnum in the above Data.

Is this what you required!

PFA,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
Highlighted

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

Can you share the script that you have been using?

Highlighted
Valued Contributor III

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

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

Highlighted
Honored Contributor

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

Hi,

i didn't got you ,Please elaborate!!

-Hirish

HirisH
“Aspire to Inspire before we Expire!”
Highlighted
Contributor

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

Hi Tersesco,

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