Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Read about Qlik’s Response to COVID-19 Read the Letter, Join the Group.
Highlighted
Contributor

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

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



Tags (1)
1 Solution

Accepted Solutions
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

28 Replies
Highlighted
MVP
MVP

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

Try like:

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

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

Highlighted
MVP
MVP

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

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)

Highlighted
Contributor

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

Hi Maxgro,

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

Highlighted
Contributor

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

Hi Trsesco,

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

Highlighted

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

Try with null like below

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

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.

Not working Anil

Highlighted
Contributor

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

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

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

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

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
Honored Contributor

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

Hi,

Can you post your'e required output!

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

-Hirish

HirisH
“Aspire to Inspire before we Expire!”