"Join" tables only if a value has changed

[pedrohenriqueperna]

Hi!

I'm trying to join rows from a second table only if the values for quantity have changed. I'm not sure how to do it using joins. Any help is appreciated.

The idea is to have a stock historic table containing the last date that the quantity of a certain SKU has changed. Therefore, if there's no change the date should remain the same as before.

Table 1 would look like this:

Table 2 will hold the data of the extraction for every SKU and look like this:

The result table will only replace the rows for the SKUs that had a change in the quantity value, otherwise it will show the old date:

[marcus_sommer]

I think by this approach you will need to merge always all SKU-dates and evaluating afterwards the max. date. For this a mapping would be easier as a join, for example:

m: mapping load SKU, date from Transactions;

Stock: load SKU, rangemax(date, applymap('m', SKU, null()) as date from History;

[PrashantSangle · Accepted solution]

why don't you simply join both table and use if condition for date on resident table.

like

temp:

Load SKU, QTD, update_date from table1;

Join

Load SKU, QTD as QTD1, update_date as update_date_new from table2

Noconcatenate

Final:

Load SKU, if(QTD<>QTD1,QTD1,QTD) as QTD, if (QTD<>QTD1,update_date_new,update_date) as update_date

Resident Temp;

Drop table Temp;

Regards,

Prashant Sangle

[pedrohenriqueperna]

Hi Prashant!

That's exactly what I was trying to do, except I wasn't sure about the NoConcatenate. Thank you very much, it worked perfectly!!

[pedrohenriqueperna]

Hi, Marcus!

Thank you for the reply. I will sure try that approach as well!