Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

hkg_qlik
Contributor II

Trim Field Values

Hi,

I need to trim some field values in a product table. All products are having their name starting with 'PR'.

Example (PRODUCT TABLE): PRFOOD, PRDRINKS, PDFLAVORS etc

I need to remove 'PR' from front of all above names while loading the script.

Regards,

H

Tags (1)
1 Solution

Accepted Solutions
kangaroomac
Contributor II

Re: Trim Field Values

Hiya,

Another option could be:

Load
    
ProductID
     ,
SubField(ProductName,'PR',2)     AS NewProductName;

SQL SELECT
      Product ID
     , ProductName
FROM ProductsTable;

Hope this helps.

6 Replies
MVP
MVP

Re: Trim Field Values

Hi,

You simply need to alias the field names using the AS statement, eg:

LOAD
  PRFOOD as Food,

   PRDRINKS as Drinks,

   PRFLAVORS as Flavors,

When I have many many fields that I have to do this sort of thing for I tend to paste the field list into Excel then in an adjoining cell have an expression to build the load script for me, eg:

=CONCATENATE("     ", A1, " AS [", PROPER(MID(A1, 3, 99)), "],")

This can then be copied down for each field in Excel, the column containing all the values can then be copied and pasted directly into your load script.

Hope that helps,

Steve

hkg_qlik
Contributor II

Re: Re: Trim Field Values

Hi Steve,

My table looks like this:

PRODUCT TABLE has following values where I need to remove PR

PRFOOD

PRDRINKS

PRFLAVORS

Thanks,

H

Sokkorn
Honored Contributor

Re: Trim Field Values

Hi H,

Maybe I'm got you wrong, but let try:

[Product]:

Load

     ProductID,

     ProductName,

     Mid(ProductName,3,Len(ProductName))     AS NewProductName;

Select ProductID,ProductName From PRODUCT;

Regards,

Sokkorn

Not applicable

Re: Trim Field Values

Are you taking about Field Names or Field Values?

If you want rename the Field names, just follow the Steve procedure. If you want change the field values, Please use RIGHT function: RIGHT(FIELNAME, LEN(FIELDNAME)-2) AS FIELDNAME

kangaroomac
Contributor II

Re: Trim Field Values

Hiya,

Another option could be:

Load
    
ProductID
     ,
SubField(ProductName,'PR',2)     AS NewProductName;

SQL SELECT
      Product ID
     , ProductName
FROM ProductsTable;

Hope this helps.

MVP
MVP

Re: Trim Field Values

Hi,

Sorry, I thought those values were column names. Sokkorn's suggestion is

fine.

Mid(PRODUCT, 3, 99) as Product,

Steve

Community Browser