Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to combine two fields into one, in the load script?

In our sales data we have the STATE_FIPS code and the COUNTY_FIPS code as independent fields, but I need one field created that combines these two fields with a hyphen.  I've tried various things like STATE_FIPS & '-' & COUNTY_FIPS to show 10-029 for example, but can't get it to work.  The other thing that may be going on is QV could be seeing each of these independent fields as numbers rather than text.

Tags (1)
1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: How to combine two fields into one, in the load script?

Are you doing this in SQL statement?  Your initial attempt

STATE_FIPS & '-' & COUNTY_FIPS

is for QV script, no for SQL.  In SQL, you can do this:

STATE_FIPS + '-' + COUNTY_FIPS

8 Replies
Not applicable

Re: How to combine two fields into one, in the load script?

Hi,

You may try: text(STATE_FIPS)&'-'&text(COUNTY_FIPS) as NewField

Regards,

Gerrit

Not applicable

Re: How to combine two fields into one, in the load script?

That should work

carbal1952
Contributor II

Re: How to combine two fields into one, in the load script?

First I try, then I'd be sure.

I always prefer my face turns red before than green after.

CB.

Not applicable

Re: How to combine two fields into one, in the load script?

Thanks Gerrit.  I tried this before too and just now again, and receive an error message that 'TEXT' is not a recognized built-in function name.

mov
Esteemed Contributor III

Re: How to combine two fields into one, in the load script?

Are you doing this in SQL statement?  Your initial attempt

STATE_FIPS & '-' & COUNTY_FIPS

is for QV script, no for SQL.  In SQL, you can do this:

STATE_FIPS + '-' + COUNTY_FIPS

Not applicable

Re: How to combine two fields into one, in the load script?

Michael,

Thank you so much!  Forgive me, but I am trying to learn all of this on my own and do not know SQL either, so I am learning that too.  I was in Edit Script, but I guess loading a table from our data warehouse is using the SQL and loading from as Excel file is using the QV script.  So, I had the SQL at the start of the load for this table and so I used your second option above:

SQL SELECT "ORDER_NO",

    COMPANY,

    "ST_FIPS",

    "CTY_FIPS",

    "ST_FIPS"+'-'+"CTY_FIPS" as FIPS,

    "RECORD_TYPE",

    SOURCE

FROM "IFSDW_PROD".dbo."Dw_FACT_DISTRIBUTOR_SALES";

And IT WORKED!!  Thanks again as I had been trying everything I could find or think of trying!  Hopefully, this helps others as well.

mov
Esteemed Contributor III

Re: How to combine two fields into one, in the load script?

Shelly,

Some additional info...

SQL statements use SQL syntax, they don't understand QV syntax.  The suggestion from Gerrit Zanen and your response were helpful to identify the problem.

If you want to use QV functionality, you still can do it using preceding load:

LOAD

    "ORDER_NO",

   COMPANY,

    "ST_FIPS",

    "CTY_FIPS",

    "ST_FIPS"&'-'&"CTY_FIPS" as FIPS,

    "RECORD_TYPE",

    SOURCE;

SQL SELECT

    "ORDER_NO",

    COMPANY,

    "ST_FIPS",

    "CTY_FIPS",

    "RECORD_TYPE",

    SOURCE

FROM "IFSDW_PROD".dbo."Dw_FACT_DISTRIBUTOR_SALES";

In your case it is not necessary, but often you may need other QV functions that don't have analogs in SQL, and preceding load may be helpful.

Regards,

Michael

Edit:  Loading from the databases, use SQL syntax.  Loading from excel and other files, resident load, preceding load - QV syntax.

Not applicable

Re: How to combine two fields into one, in the load script?

Michael,

Thanks for this too.  I agree that this will come in handy as you mention.

It seems like there's a lot of power in the script, but I am finding that it is one of the hardest things to figure out and learn with QlikView - even trying to use the actual QV reference manual.  The power the script has seems to make it more technical, but I'm getting there!

Awesome input!

Community Browser