Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

rowno () in script side depending upon specific columns

HI All,

I want to write RowNO () funcion in script side depending upon Workcenter , Funcion Location

Administrator

1 Solution

Accepted Solutions
MVP
MVP

Re: rowno () in script side depending upon specific columns

Try below in your script...

Temp:

Load

  Workcenter,

  [Funcional Location],

  Date(Date#(Date,'DD MMM YYYY')) as Date,

  Workcenter&[Funcional Location] as Key

Inline

[

Workcenter,   Funcional Location,  Date          

A,            X,                   10 Apr 2012

A,            X,                   12 Apr 2012

B,            Y,                   19 Apr 2013

B,            Y,                   10 June 2013       

];

NoConcatenate

Final:

Load

  Workcenter,

  [Funcional Location],

  Date,

  IF(Key = Previous(Key), RangeSum(Peek(Sequence)+1),1) as Sequence

Resident Temp

Order By Workcenter, [Funcional Location], Date;

Drop Table Temp;

16 Replies
anbu1984
Honored Contributor III

Re: rowno () in script side depending upon specific columns

source:

load * inline [

Workcenter , Funcion Location

...

];

table:

load AutoNumber(Workcenter & [Funcion Location], Workcenter) as Sequence,

*

Resident source

order by Workcenter,[Funcion Location];

DROP Table source;

MVP
MVP

Re: rowno () in script side depending upon specific columns

Kindly provide some sample of your field or data....otherwise as anbu suggested, use Autonumber function script side

Not applicable

Re: rowno () in script side depending upon specific columns

HI Manish,

I have WOrkcenter, Funcional location and against that dates are there so i want serial no for that dates.

EX :

Workcenter   Funcional Location  Date               Sequence

      A               X                        10 Apr 2012       1

      A               X                        12 Apr 2012       2

     B                Y                        19 Apr 2013      1 

     B                 Y                       10 June 2013    2                    

MVP
MVP

Re: rowno () in script side depending upon specific columns

Try below in your script...

Temp:

Load

  Workcenter,

  [Funcional Location],

  Date(Date#(Date,'DD MMM YYYY')) as Date,

  Workcenter&[Funcional Location] as Key

Inline

[

Workcenter,   Funcional Location,  Date          

A,            X,                   10 Apr 2012

A,            X,                   12 Apr 2012

B,            Y,                   19 Apr 2013

B,            Y,                   10 June 2013       

];

NoConcatenate

Final:

Load

  Workcenter,

  [Funcional Location],

  Date,

  IF(Key = Previous(Key), RangeSum(Peek(Sequence)+1),1) as Sequence

Resident Temp

Order By Workcenter, [Funcional Location], Date;

Drop Table Temp;

MVP
MVP

Re: rowno () in script side depending upon specific columns

Try

...

autonumber(recno(), Workcenter) as Sequence,

...

Re: rowno () in script side depending upon specific columns

Try to load your table like below script

=================

Raw:

LOAD * INLINE [

    Workcenter, Funcional Location, Date

    A, X, 10 Apr 2012

    A, X, 12 Apr 2012

    B, Y, 19 Apr 2013

    B, Y, 10 June 2013

];

Final:

LOAD *,RecNo() as Sequence;

LOAD

Workcenter,   [Funcional Location],  Date

Resident Raw Where Right(Date,4) = 2012;

Concatenate

LOAD *,RecNo() as Sequence;

LOAD

Workcenter,   [Funcional Location],  Date

Resident Raw Where Right(Date,4) = 2013;

DROP Table Raw;

See the  snap for result

Seq.png

Not applicable

Re: rowno () in script side depending upon specific columns

Hi Manish ,

Thankx . It's wprking perfect. But now I want following scenario,

I want to calculate MTBF in that I want difference between first breakdown of the month and last brekdown of previous date .

But other columns are having expressions monthly then date for previous month won't appear in date field but i want difference

EX :

I am having data like this (INput)

Workcenter   Functional Location     Date                QTY (Expression for the month March)

A                  X                               02/28/2014                      10

B                  Y                              03/12/2014                     20     

C                  Z                              03/20/2014                    30

I want Output like this (I want to show only March data as march is selected in calnder)    

Workcenter   Functional Location     Date                               Difference   QTY (Expression for the month March)

B                  Y                              (03/12/2014-02/28/2014)        12                  20     

C                  Z                              (03/20/2014 -03/12/2014)        8                   30    

MVP
MVP

Re: rowno () in script side depending upon specific columns

Something like below...

Temp:

Load * Inline

[

  Workcenter,   Functional Location, Date, QTY

  A,            X,                    02/28/2014, 10

  B,            Y,                    03/12/2014, 20    

  C,            Z,                    03/20/2014, 30

];

NoConcatenate

Final:

Load

  RowNo(),

  Workcenter,  

  [Functional Location],

  (Date-Previous(Date)) as Date,

  QTY

Resident Temp

Where NOT IsNull(Date-Previous(Date))

Order By Date;

Drop Table Temp;

Not applicable

Re: rowno () in script side depending upon specific columns

Hi Manish,

Sorry I didn't get output can you provide me sample qlikview file