Solved: Re: Generating data Month and ID wise from above r... - Qlik Community

ankitbisht01 · ‎2016-10-21

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

Anonymous · ‎2016-10-24

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

tresesco · ‎2016-10-21

Try like:

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

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

maxgro · ‎2016-10-21

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 · ‎2016-10-22

Hi Maxgro,

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

ankitbisht01 · ‎2016-10-22

Hi Trsesco,

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

Anil_Babu_Samineni · ‎2016-10-22

Try with null like below

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

ankitbisht01 · ‎2016-10-22

Not working Anil

ankitbisht01 · ‎2016-10-22

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

Anil_Babu_Samineni · ‎2016-10-22

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

HirisH_V7 · ‎2016-10-23

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!”

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