Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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.

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

8 Replies
Not applicable
Author

Hi,

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

Regards,

Gerrit

Not applicable
Author

That should work

Anonymous
Not applicable
Author

First I try, then I'd be sure.

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

CB.

Not applicable
Author

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
Not applicable
Author

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
Author

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
Not applicable
Author

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
Author

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!