compare and combine 2 product indexes if no difference found

[markthepig]

Hello there Community

I have the following scenario.

I get once per day a file (.csv) that contains productcode and productname

ex:

productcode,productname

"0001","Coca Cola 2L"

"0002","Sprite 2L"

"0003","Red Bull 250ml"

Next day i get a file with new products added (but some times on the productcode that i received a day before it;s a new product name)

ex

productcode,productname

"0001","Coca Cola 2L"

"0002","Sprite 2L"

"0003","New Product"   <---- This is changed.

"0004","Sierra Myst 2L"

"0005","7 up 2L"

I want to load the files and detect if they changed product name on the same code. If nothing changed i want to save the new file that contains all the products.

Thank you for taking the time to help.

Yours Mark

[marcus_sommer]

I would say it's not a productcode it's more a row-number and you should try to get a real product-id or at least how these data are connected with other data like dates, sales and so on.

- Marcus

[markthepig]

Thank you for answer, but is;s a product code. Sometimes they modified the product name and i don't know why. It affects the invoices (i get invoices that are related to the product-code). I need to know if they changed the codes so i can create a new code for the old invoices. Thank you

[swuehl · Accepted solution]

Maybe something like

OLD:
LOAD productcode,
    productname
FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);


NEW:
NOCONCATENATE
LOAD productcode, productname, if(oldname <> productname, oldname) as oldname;
LOAD productcode,
    productname,
    if(exists(productcode), lookup('productname','productcode',productcode,'OLD') ) as oldname
FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);


DIFF:
LOAD
  Concat(productcode & ': Old = ' & oldname & '  new = ' & productname, chr(10)) as Diff
RESIDENT NEW
WHERE len(oldname)
GROUP BY productcode;


Let vDiffFound = NoOfRows('DIFF');


Drop table OLD;


if vDiffFound then
  exit script;
ELSE
  drop field oldname;
  STORE NEW into ALL.qvd (qvd);


ENDIF

[avinashelite]

hi,

Please find the attached qvw for the solution.

NOTE: I have based this logic on the file created date , since your csv file created on the same day I have added 1day difference in the script that you can remove and test with the real time files

[avinashelite]

Temp:

LOAD productcode,

     productname,

     FileTime() as Time

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

LOAD productcode,

     productname,

     FileTime() as Time

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Result:

Load num(Max(Time)) as Time_key,

   productcode  as code

   Resident

   Temp

   Group by productcode;

   //Final_result:

   Load productcode,

     productname,

     num(Time) as Key

   Resident

   Temp

   where Exists(Time_key,num(Time));

   drop Table Temp;

[marcus_sommer]

A slight different approach to the solution from swuehl and in opposite to his it's my generic and not tested. But I think the direction with adding an from_date and to_date could be helpful if related per intervalmatch to a master-calendar:

t1:

Load

productcode & '|' & productname as key, productcode, productname,

date(date#(mid(filebasename(), 4), 'DD-MM-YYYY'), 'DD.MM.YYYY') as date

From path & day*.csv where not exists(key, productcode & '|' & productname);

t2:

Load

key, productcode, productname, date as from_date,

if(productcode <> peek('productcode'), '31.12.9999' /* default-value */, peek('date')) as to_date

Resident t1 order by productcode desc, date;

drop table t1;

- Marcus