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

[#1]

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.

[#2]

Hi,

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

Regards,

Gerrit

[#3]

That should work

[Anonymous]

First I try, then I'd be sure.

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

CB.

[#5]

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.

[Anonymous · Accepted solution]

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

[#7]

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.

[Anonymous]

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.

[#9]

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!