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

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

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

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

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

Account  Month     Status

C000001 Oct-2016      2

C000002 May-2010     -

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

Regards!!

Highlighted
Honored Contributor

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

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

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

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

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

Highlighted
Contributor

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

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??


Highlighted
Valued Contributor III

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

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!

Highlighted
Valued Contributor III

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

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!

Highlighted
Contributor

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

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;

Highlighted
Valued Contributor III

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

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;