topic Re: How can I get only one rows per type ? in QlikView

How can I get only one rows per type ?

suzel404 — Thu, 01 Jan 2015 16:59:59 GMT

Hi all

I have a sample table :

LOAD

ID , TYPE, MODE , DATE

FROM TABLE.QVD(QVD);

My data is :

0001 , 1 , 10 , 13/02/2014

0001 , 1 , 11, 15/02/2014

0001 , 2 , 22 , 21/02/2014

0001 , 2 , 23 , 22/02/2014

0001 , 2 , 24 , 22/02/2014

0001 , 4 , 40 , 10/03/2014

0001 , 4 , 41 , 11/03/2014

0001 , 4 , 42 , 12/03/2014

0001 , 4 , 43 , 13/03/2014

I want the table with this data

0001 , 1 , 10 , 13/02/2014

0001 , 2 , 22 , 21/02/2014

0001 , 4 , 40 , 10/03/2014

I just want one line per TYPE.

Thank for your help.

Re: How can I get only one rows per type ?

its_anandrjs — Thu, 01 Jan 2015 17:47:11 GMT

Hi,

Try this script and create another table for Min dates also and then finally load for min dates rows here i assume you need the rows which has minimum dates. If this is requirement then try this way.

T1:

LOAD ID, TYPE, MODE, Date#(DATE,'DD/MM/YYYY') as DATE;

LOAD * INLINE [

ID, TYPE, MODE, DATE

0001, 1, 10, 13/02/2014

0001, 1, 11, 15/02/2014

0001, 2, 22, 21/02/2014

0001, 2, 23, 22/02/2014

0001, 2, 24, 22/02/2014

0001, 4, 40, 10/03/2014

0001, 4, 41, 11/03/2014

0001, 4, 42, 12/03/2014

0001, 4, 43, 13/03/2014

];

Left Join

LOAD

ID, TYPE,

Date(Min(DATE),'DD/MM/YYYY') as MinDate

Resident T1 Group By ID,TYPE;

NoConcatenate

T2:

LOAD ID, TYPE, MODE, DATE Resident T1 Where DATE = MinDate;

DROP Table T1;

Regards

Anand

Re: How can I get only one rows per type ?

udit_kumar_sana — Thu, 01 Jan 2015 17:54:59 GMT

Hi,

Pls find the attachment for the answer.

Thanks & Regards,

Udit

Re: How can I get only one rows per type ?

rwunderlich — Thu, 01 Jan 2015 18:34:48 GMT

How about:

data:

LOAD

TYPE,

Date(min(DATE)) as DATE

FROM TABLE.qvd (qvd)

GROUP BY TYPE;

LEFT JOIN (data)

LOAD DISTINCT * FROM TABLE.qvd (qvd);

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: How can I get only one rows per type ?

maxgro — Thu, 01 Jan 2015 19:12:24 GMT

TABLE:
load * from TABLE.qvd (qvd);

Final:
NoConcatenate load
  ID, TYPE, MODE, DATE
Resident TABLE
where Peek(TYPE)<>TYPE 
order by ID, TYPE;

DROP Table TABLE;

Re: How can I get only one rows per type ?

nagaiank — Thu, 01 Jan 2015 19:55:05 GMT

The following script does what you want to do.

T1:

LOAD ID, TYPE, as TYPE1, MODE, DATE Where Not Exists(TYPE1,TYPE);

LOAD * INLINE [

ID, TYPE, MODE, DATE

0001, 1, 10, 13/02/2014

0001, 1, 11, 15/02/2014

0001, 2, 22, 21/02/2014

0001, 2, 23, 22/02/2014

0001, 2, 24, 22/02/2014

0001, 4, 40, 10/03/2014

0001, 4, 41, 11/03/2014

0001, 4, 42, 12/03/2014

0001, 4, 43, 13/03/2014

];

RENAME Field TYPE1 to TYPE;

Re: How can I get only one rows per type ?

suzel404 — Thu, 01 Jan 2015 20:24:10 GMT

Thanks you for your response.

But the result doesn't work well with my data.

I don't have a uniq rows per TYPE_ID.

Re: How can I get only one rows per type ?

nagaiank — Thu, 01 Jan 2015 23:45:50 GMT

The attached application produces the following result for your data. Is this your requirement?

Re: How can I get only one rows per type ?

Fri, 02 Jan 2015 00:33:47 GMT

Joshua,

I believe in your case is not a matter of ETL procedure, I guess QlikView can solve your problem or whatever you are trying to achieve internally using the QlikView Engine itself. For instance, it is very easy to show your result by using the following.

1. Use a CHART called straight table

2. Add two Dimensions, ID, TYPE_ID , this ensures Qview created a temp table based on these two columns with the following 3 expressions , yes 3 🙂

3. Create the expressions as follows.

MODE_ID definition: IF (MIN(CREATION_DATE)=CREATION_DATE,MODE_ID,)

label it: MODE_ID

CREATION_DATE definition: IF (MIN(CREATION_DATE)=CREATION_DATE,CREATION_DATE,)

label it: CREATION_DATE

CALENDAR_DATE definition: IF (MIN(CREATION_DATE)=CREATION_DATE,CALENDAR_DATE,)

label it: CALENDAR_DATE

Here is the screen shot and attached qv file

Re: How can I get only one rows per type ?

suzel404 — Fri, 02 Jan 2015 08:42:35 GMT

Thank you guys!

Mario I agree with you but in my BI team all instructions MUST BE in script.

I don't have choice.

Thank you very much and happy new year!!