topic Re: Re: How to create another field in an existing table in QlikView

How to create another field in an existing table

Anonymous — Tue, 18 Feb 2014 19:04:00 GMT

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);

Re: How to create another field in an existing table

Tue, 18 Feb 2014 19:06:04 GMT

Hi,

you missed attachment in your post.

Re: How to create another field in an existing table

Gysbert_Wassenaar — Tue, 18 Feb 2014 19:10:47 GMT

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);

Re: How to create another field in an existing table

Anonymous — Tue, 18 Feb 2014 19:16:14 GMT

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!

Re: How to create another field in an existing table

Tue, 18 Feb 2014 19:33:56 GMT

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);

Re: Re: How to create another field in an existing table

makkemik — Tue, 18 Feb 2014 19:45:39 GMT

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