Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI All,
I want to write RowNO () funcion in script side depending upon Workcenter , Funcion Location
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;
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;
Kindly provide some sample of your field or data....otherwise as anbu suggested, use Autonumber function script side
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
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;
Try
...
autonumber(recno(), Workcenter) as Sequence,
...
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
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
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;
Hi Manish,
Sorry I didn't get output can you provide me sample qlikview file