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

rowno () in script side depending upon specific columns

Thu, 15 May 2014 05:50:04 GMT

HI All,

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

Administrator

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

anbu1984 — Thu, 15 May 2014 06:07:06 GMT

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;

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

MK_QSL — Thu, 15 May 2014 06:09:11 GMT

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

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

Thu, 15 May 2014 06:43:08 GMT

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

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

MK_QSL — Thu, 15 May 2014 07:24:54 GMT

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;

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

swuehl — Thu, 15 May 2014 07:26:57 GMT

Try

...

autonumber(recno(), Workcenter) as Sequence,

...

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

its_anandrjs — Thu, 15 May 2014 07:30:24 GMT

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

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

Thu, 15 May 2014 09:10:28 GMT

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

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

MK_QSL — Thu, 15 May 2014 09:23:34 GMT

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;

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

Thu, 15 May 2014 09:53:06 GMT

Hi Manish,

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

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

jagannalla — Thu, 15 May 2014 10:04:42 GMT

Hi,

Try this, but what is the calculation for Diff ?

LOAD Workcenter,[Funcional Location],Text(Date) as Date,AutoNumber(RecNo(),Workcenter) as Seq;

LOAD * INLINE [

Workcenter, Funcional Location, Date, Exp

A, X, 02/28/2014,10

B, Y, 03/12/2014,20

C, X, 03/20/2014,30

];

LOAD Date,

if(not IsNull(Peek(Date)),Date&'-'&Peek(Date)) as PrDate

Resident T

Order By Date

;

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

MK_QSL — Thu, 15 May 2014 10:17:01 GMT

PFA

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

Thu, 15 May 2014 10:47:42 GMT

Hi Manish,

Please find an attachment file. In that Output column is my expected output.

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

MK_QSL — Thu, 15 May 2014 10:55:24 GMT

attachment is missing !

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

Thu, 15 May 2014 11:14:39 GMT

HI Manish,

Please find an attachment in that I want Output column as expected ouptput.

BUt I want it script side.

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

Thu, 15 May 2014 11:16:24 GMT

Hi Manish,

Please find an attachment. I want to do it script side.

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

Thu, 15 May 2014 13:00:45 GMT

Hi All,

Please find an attachment I want output column in script side,.