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

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

ankitbisht01 — Wed, 25 Nov 2020 16:16:04 GMT

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

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

tresB — Fri, 21 Oct 2016 12:12:05 GMT

Try like:

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

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

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

maxgro — Fri, 21 Oct 2016 12:16:14 GMT

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)

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

ankitbisht01 — Sat, 22 Oct 2016 13:39:35 GMT

Hi Maxgro,

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

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

ankitbisht01 — Sat, 22 Oct 2016 13:40:35 GMT

Hi Trsesco,

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

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

Anil_Babu_Samineni — Sat, 22 Oct 2016 14:54:46 GMT

Try with null like below

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

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

ankitbisht01 — Sat, 22 Oct 2016 18:47:56 GMT

Not working Anil

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

ankitbisht01 — Sat, 22 Oct 2016 18:48:37 GMT

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.

Anil_Babu_Samineni — Sun, 23 Oct 2016 02:57:59 GMT

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

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

HirisH_V7 — Sun, 23 Oct 2016 12:24:02 GMT

Hi,

Can you post your'e required output!

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

-Hirish

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

ankitbisht01 — Sun, 23 Oct 2016 18:28:32 GMT

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

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

tresB — Mon, 24 Oct 2016 04:19:23 GMT

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

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

Anil_Babu_Samineni — Mon, 24 Oct 2016 04:28:38 GMT

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:

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

ankitbisht01 — Mon, 24 Oct 2016 07:29:33 GMT

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.

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

Anonymous — Mon, 24 Oct 2016 08:01:57 GMT

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!

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

HirisH_V7 — Mon, 24 Oct 2016 08:15:54 GMT

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

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

sunny_talwar — Mon, 24 Oct 2016 08:35:43 GMT

Can you share the script that you have been using?

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

Anonymous — Mon, 24 Oct 2016 08:48:01 GMT

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

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

HirisH_V7 — Mon, 24 Oct 2016 08:50:09 GMT

Hi,

i didn't got you ,Please elaborate!!

-Hirish

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

ankitbisht01 — Mon, 24 Oct 2016 08:52:05 GMT

Hi Tersesco,

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