I have problem whit field value from onother tabel

[#1]

I have sales values in one textfile and i wont to use a value in this textfile in a if to select right unit and price.

let mee explain

textfile 1 (sales)

Godsnumber, Godsname, sales_number, sales_price, sales_unit

111,                Qlik,            1,                      10,           apiece

textfile 2 (Gods inform)

Godsnumber, Godsname, sales_unit1, sales_price1, sales_unit2, sales_price2,sales_unit3,      sales_price3,

111,                Qlik,             apiece,        10,                  package1,    50,              Largepackage,   100

I wont to get the right unit and price from textfile2 depend of what salesunit used in textfile1 the price in textfile2 is the right price and the price in textfile1 can be wrong.

Hope someone can help mee how to do that

Thanks Leif

[marcus_sommer]

If you have distinct rows you could use a join, for example:

match:

load * From textfile1;

     left join

load Godsnumber, sales_unit1, sales_price1 From textfile2;

An alternatively would be to use a mapping-approach, like:

mapping:

mapping load Godsnumber, sales_unit1 & '|' & sales_price1 From textfile2;

match:

load *,

     subfield(applymap('mapping', Godsnumber), '|', 1) sales_unit1 as 

     subfield(applymap('mapping', Godsnumber), '|', 2) sales_price1 as

From textfile1;

- Marcus

[#3]

Yes if i alway use sales_unit1 in textfile2 but it is diffrent unit in evry line in textfile1

So next line i might wont onother unit (I have 8 diffrents units in textfile2 and one in textfile1 so i wont to pick out the right one unit from textfile2 too get the right price)

i Hope i clear out some of my problems.

/Leif

[marcus_sommer]

For this case you will need the information in textfile1 which unit and price you need to grab from textfile2 - do you have it there?

Very probably I would transform the textfile2 with a The Crosstable Load and then applying a mapping load with them - similar to the above one only that you now grabbed a combined key of your Godsnumber and the unit-information.

- Marcus

[#5]

textfil1 is a very large file whit evry transaction in sales (History) whit many columns and i missing the sourse price there. textfile2 is the godsfile(itemfile) whith every item for sales and that evry item could be sold in 8 diffrent units (very big to) whit the sourse price i wont too pick.

So i wont  to pick the sourse price one textfile2 depend of the unit the item is sold in textfile1.
So can you explain howe too do that, so easy and efficiently as possiby.

And thanks for You Answer
/Leif

[marcus_sommer]

I think the important point is to transform your textfile2 into a reasonable data-structure to be able to generate a mapping-table which could be match with yor transaction-data from textfile1. Surely there will be different ways possible to get these result but I would try it like in the following.

It's not tested and there could be some syntax-issues and/or the logic needs some adjustement but in general the approach should work.

For this I transformed the table with a crosstable-statement, then I made some extractions to ensure that I could sort it to the Godsnumber and UnitPriceID and UnitPriceType which I need to get the belonging Unit and Price in one record again (per previous-function, very useful: Peek() or Previous() ?) and throw away the garbage-record and create the mapping-table: Mapping … and not the geographical kind.

Crosstable: // The Crosstable Load

Crosstable(Category, Value, 2) Load * From textfile2;

PrepareOrderBy:

Load

     Godsnumber, Value,

     keepchar(Category, '0123456789') as UnitPriceID, left(Category, 1) as UnitPriceType

Resident Crosstable;

OrderBy:

Load

     Godsnumber, Value as UnitType, previous(Value) as SalesPrice,

     mod(rowno(), 2) as Filter

Resident PrepareOrderBy Orderby  Godsnumber, UnitPriceID, UnitPriceType;

Mapping:

Load Godsnumber & '|' & UnitType as MappingKey, SalesPrice

Resident OrderBy Where Filter = 0;

drop tables Crosstable, PrepareOrderBy, OrderBy;

match:

load *,

     applymap('Mapping', Godsnumber & '|' & UnitType) as  SalesPriceFromTextFile2

From textfile1;

- Marcus