Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

script to get the record related to min date for each type(non key field) using QV builtin functions

Hello All,

PFB th scenario.

Table 1
ObjectDateType
O1D11
O2D21
O3D32
Table 2
ArticleObject
AO1
AO2
BO1
BO3
Join
ArticleObjectDateType
AO1D11
AO2D21
BO1D11
BO3D32
Required
  result
ArticleObjectDateType
AO1D11
BO1D11
BO3D32

whenever an article has the same Type, the For each article, the Type with the minimum date is to be retained and the rest are to be excluded.

Could you please suggest how this can be achieved in script, without using a group by and by using QV functions.

Thanks!

1 Solution

Accepted Solutions
anbu1984
Honored Contributor III

Re: script to get the record related to min date for each type(non key field) using QV builtin functions

Try this

T1:

LOAD Object ,Date#(Date,'DD/MM/YYYY') As Date ,Type INLINE [

    Object ,Date ,Type

    O1 , 01/01/2015, 1

    O2 , 15/01/2015, 1

    O3 , 06/01/2015, 2

];

Join

LOAD * INLINE [

    Article, Object

    A, O1

    A, O2

    B, O1

    B, O3

];

NoConcatenate

T1_F:

Load *,If(Article <> Peek(Article),1,If(Type <> Previous(Type),1,0)) As Flag Resident T1 Order by Article,Type,Date;

Drop Table T1;

12 Replies
mrossoit
Contributor II

Re: script to get the record related to min date for each type(non key field)

Try to add:

RESULT:

Load distinct Article,

min(Date) as Date,

Type

Resident MY_JOINED_TABLE;

left join

load *, '' as tmp resident MY_JOINED_TABLE;

drop table MY_JOINED_TABLE;

drop field tmp;

Regards

MR

anbu1984
Honored Contributor III

Re: script to get the record related to min date for each type(non key field)

Table2:

Load Article,Object From Table2;

Inner Join(Table2)

Load Object,Min(Date) As Date,Type From Table1 Group by Object,Type;

bismart
Contributor

Re: script to get the record related to min date for each type(non key field)

T1:

LOAD * INLINE [

    Object ,Date ,Type

    O1 , 01/01/2015, 1

    O1 , 15/01/2015, 1

    O3 , 06/01/2015, 2

]
;



Join

LOAD * INLINE [

    Article, Object

    A, O1

    A, O2

    B, O1

    B, O3

]
;



T2:

NoConcatenate Load

*,

Article & '-' & Type as Key

Resident T1;

DROP TABLE T1;



Table:

Noconcatenate Load Distinct

Key,

Article,

Object,

Min(Date) as Date,

Type

resident T2

where LEN(Date)<>0

group by

Key,

Article,

Object,

Type;


DROP TABLE T2;

Not applicable

Re: script to get the record related to min date for each type(non key field)

Hello All,

Thanks for your replies,

I want to avoid group by and do it using QV built in functions.

Is there a way?

Not applicable

Re: script to get the record related to min date for each type(non key field)

Hello All,

Thanks for your replies,

I want to avoid group by and do it using QV built in functions.

Is there a way?

mrossoit
Contributor II

Re: script to get the record related to min date for each type(non key field) using QV builtin functions

What do you mean with QV built in functions?

Group by belongs to QV loading syntax.

Are you trying to do it directly in your sheet object avoiding scripting elaborations?

Not applicable

Re: script to get the record related to min date for each type(non key field) using QV builtin functions

Hello Rosso,

I mean, i wanted to use some if and peek condition and set a flag value.

And based on the flag value, i want to identify which records are to be retained and which are to be excluded.

Something like this.

Thanks!

mrossoit
Contributor II

Re: script to get the record related to min date for each type(non key field) using QV builtin functions

I think you can't do it without using Group by. You have to identify rows with minimum date for the same type and to do this you need to use min() which is an aggregation function.

Aggregation functions need Group by to work.

Regards

MR

Not applicable

Re: script to get the record related to min date for each type(non key field) using QV builtin functions

This is not yielding correct output.

Its giving all the records of table1