How can I get only one rows per type ?

[suzel404]

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.

[its_anandrjs · Accepted solution]

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

[udit_kumar_sana]

Hi,

Pls find the attachment for the answer.

Thanks & Regards,

Udit

[rwunderlich]

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

[maxgro]

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;

[nagaiank]

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;

[suzel404]

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.

[nagaiank]

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

[#9]

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

[suzel404]

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!!