How to create another field in an existing table

[Anonymous]

Hi Folks,

I have an invoice table loaded. One of the field is "Product Name". I am trying to add another field called "Product Group". (Please see attached file. )

The current script is as below,

InvoiceTable:

Load

InvoiceTable.ProductName,

InvoiceNumber,

InvoiceTable.Amount

From

[..\..\OrderDetail.qvd]

(qvd);

[#2]

Hi,

you missed attachment in your post.

[Gysbert_Wassenaar]

I don't see any attached file.

If the Product Group field is derived (calculated) from the Product Name field you can do it in the same load:

InvoiceTable:
Load
InvoiceTable.ProductName,
InvoiceNumber,
InvoiceTable.Amount,
subfield(InvoiceTable.ProductName, '\', 1) as ProductGroup // this is just an imaginary example
From
[..\..\OrderDetail.qvd]
(qvd);

If you get the information from another source you can load it in another table

Products:
Load ProductName as InvoiceTable.ProductName, ProductGroup
From 
[..\..\Products.qvd]
(qvd);

Or you could join the information the the InvoiceTable:

join(InvoiceTable)
Load ProductName as InvoiceTable.ProductName, ProductGroup
From 
[..\..\Products.qvd]
(qvd);

Or you could first load the ProductGroups in a mapping table and use the applymap function:

MapGroups:
mapping 
Load ProductName, ProductGroup
From 
[..\..\Products.qvd]
(qvd);

InvoiceTable:
Load
InvoiceTable.ProductName,
InvoiceNumber,
InvoiceTable.Amount,
applymap('MapGroups',InvoiceTable.ProductName) as ProductGroup
 From
[..\..\OrderDetail.qvd]
(qvd);

[Anonymous]

Thanks guys,

The attachment is now added. The new field is not an existing field in the database. I have to create it on my own as mentioned in the attachment.

Please help!

[#5]

Hi,

Try with this:

InvoiceTable:

Load

InvoiceTable.ProductName,

InvoiceNumber,

Pick(WildMatch(ProductName,'M12*','N23*','GE*','TD10*'),'A','A','B','C') as PRODUCTGROUP,

InvoiceTable.Amount

From

[..\..\OrderDetail.qvd]

(qvd);

[makkemik]

Hi

You need the calculate manual in the script which is attached.

But you can make another table included productName or Code with groupCode (A,B,C) then you can join it with your main table.

I hope it will help you

Regards

Murat