Loading Records (Don't know how to explain see details).

[#1]

Hi Guys,

Here is my sample data:

ITEM NO.     PO NO.

A0001          PO-001           

A0002          PO-001

A0002          PO-002  - should not be included in the final table

A0003          PO-002

A0004          PO-003

A0004          PO-004  - should not be included in the final table

A0005          PO-005

Now what I need is to get the distinct ITEM NO, together with the corresponding PO Number of the same row.

The output table should be the following:

ITEM NO.     PO NO.

A0001          PO-001           

A0002          PO-001

A0003          PO-002

A0004          PO-003

A0005          PO-005

Hope you can help me...  thanks.

[Sokkorn]

Hi qlikering,

Try this

[Data]:
LOAD * INLINE [
ItemNo,     PO
A0001,      PO-001           
A0002,      PO-001
A0002,      PO-002
A0003,      PO-002
A0004,      PO-003
A0004,      PO-004
A0005,      PO-005];
[Data2]:
INNER JOIN ([Data])
LOAD
    ItemNo,
    'PO-'& NUM(MIN(RIGHT(PO,3)),'000')    AS PO
RESIDENT [Data] GROUP BY ItemNo;

See attached file also.

HTH and let me know.

Regards,

Sokkorn Cheav

[#3]

Hi,

Please see the attached file,i hope this will help you & let me Know

Thanks

Vivek

[#4 · Accepted solution]

Hi Vivek,

You might have attached the wrong file.

Anyway i just get the logic from Sokkorn, my script looks like as follows:

LOAD

     [PART NO.],

     'PO' & MIN(NUM(RIGHT([PO NO.],9))) AS MRP_PONO

FROM PO.xls (biff, embedded labels, table is Sheet1$) GROUP BY [PART NO.];

Regards,

[jagan]

Hi,

please try the following script

[DataTemp]:

LOAD * INLINE [

ItemNo,     PO

A0001,      PO-001          

A0002,      PO-001

A0002,      PO-002

A0003,      PO-002

A0004,      PO-003

A0004,      PO-004

A0005,      PO-005];

NoConcatenate

Data:

LOAD

    ItemNo,

    PO

WHERE IsDuplicate = 0;

LOAD

    ItemNo,

    PO,

    If(ItemNo <> Previous(ItemNo), 0, 1) AS IsDuplicate

RESIDENT DataTemp

ORDER BY ItemNo, PO;

DROP TABLE DataTemp;

Hope this helps you.

Regards,

Jagan.

[#6]

Hi,

Sorry for the wrong attachment

I hope this will simplify your logic

Thanks

Vivek

[jagan]

Hi,

This script truncates leading zeros, if it is not a problem you can use it.

For example: If PO-001, it makes it as PO-1

LOAD

     [PART NO.],

     'PO' & MIN(NUM(RIGHT([PO NO.],9))) AS MRP_PONO

FROM PO.xls (biff, embedded labels, table is Sheet1$) GROUP BY [PART NO.];

Regards,

Jagan.

[#8]

Yup actually my real PO# starts with year PO-2011 or PO-2012 so no problem with the leading zeroes. thanks guys.