Hi i have two tables Table1 and Table2, when a customer have two Pc_id, i want the latest status of the customer based on timestamp

[#1]

Hi i have two tables Table1 and Table2, when a customer have two Pc_id, i want the latest status of the customer based on timestamp

Table1

Customer_no      Pc_id       timestamp

AB123                1234        1-02-2015 10:05:00 AM

AB123                 789         17-04-2015 11:08:15 PM

XY111                  1111       17-05-2015 06:00:30 PM

MN222                 2222       27-10-2015 1:00:00 PM

Table2

Customer_no           Status

AB123                     open

AB123                     closed

XY111                     open

MN222                     closed

the output would be for customer AB123 the status is closed

[Gysbert_Wassenaar]

the output would be for customer AB123 the status is closed

Why? There's no information in Table2 that says which status is the last one.

[rubenmarin]

Hi, I don't see a way to know wich record from table 2 is related to wich record of table1.

By your last comment I know pPc_id 789 should be related to the 2nd record of Table2. If there is a 1-1 relation between Table1 and Table2 the only way I see is adding a "RowNo() as RecordNo" to the LOAD of each table and relating records based on that field values (the first record of table1 with the first record of table2) but is a weak way to join, some customers can change his status in a close timestamp and maybe the order of the records is switched between Table1 and Table2.

[rubenmarin]

Btw, if you want the last record from Table2 you can use this script:

Table2:

LOAD *, RowNo() as RecordNo INLINE [

Customer_no, Status

AB123,open

AB123,closed

XY111,open

MN222,closed

];

Table3:

NoConcatenate LOAD Customer_no, FirstSortedValue(Status, -RecordNo) as Status Resident Table2 Group By Customer_no;

DROP Table Table2;

[#5]

i have two tables

Table1:

Customer      Profile   timestamp

100                1          7/28/2015 11:54              

100                 2          9/20/2015 10:42

200                 3        9/28/2015   10:44

200                 4         9/28/2015 10:55

500                 5        10/28/2015 10:45

Table2:

Customer          Status

100                  progress

200                   cancel

500                    complete

Output:

Profile   status

1          error

2         progress

3          error

4          cancel

5          complete

in the above senario:

if the customer has two profiles for example customer 100 have two 1 and 2

we should show old profile status as error and for new one status should show as per Table2(which means the profile one has an error so we are using profile2 that we need to display in our output)

If the customer has one profile status should show as per Table2

[rubenmarin]

This script can do the trick:

Table2:

Mapping LOAd * inline [

Customer,Status

100,progress

200,cancel

500,complete

];

Table1:

LOAD Customer,Profile,timestamp#(timestamp, 'MM/DD/YYYY hh:mm') as timestamp inline [

Customer,Profile,timestamp

100,1,7/28/2015 11:54             

100,2,9/20/2015 10:42

200,3,9/28/2015 10:44

200,4,9/28/2015 10:55

500,5,10/28/2015 10:45

];

Output:

LOAD Customer,Profile,timestamp,If(Peek(Customer)=Customer, 'error', ApplyMap('Table2', Customer)) as Status

Resident Table1 Order By Customer, timestamp desc;

It returns: