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

Combining two fields from the same table to create a third unique field

Hi I was wondering how to best combine two fields in an existing data model table so that a third unique Field is created.

Eg.

Table name: fact

Field1: [Company Code]

Field2: [Profit Centre]

Field3: PCmap

I thought that I would do it like so:

LOAD

[Company Code]&[Profit Centre] as PCmap,

Resident fact;

But this doesn't work...

I have only been using QV for 3 weeks so still trying to get my head around the complexities..

Thank you in advance

8 Replies
swuehl
MVP
MVP

Hi,

your code will create a separate table, unlinked to the fact table. Before getting into deep, why not just adding the above line

[Company Code]&[Profit Centre] as PCmap,

to you original fact table load (where you also load [Company Code] and [Profit Centre]?

Then, you might want to add a seperator between the both parts (more for human readers than anything else):

[Company Code] & '-' & [Profit Centre] as PCmap,

But more performant would probably be a numeric type key, maybe using  autonumber or

Autonumberhash256 ( [Company Code],[Profit Centre]) as PCmap,

Hope that helps,

Stefan

Not applicable
Author

Thank you for the prompt response.

unfortunately the table in question is in a massive data model which I am loading in at the begining of my app.

given this what would be the next best course of action?

johnw
Champion III
Champion III

Are you loading the massive data model from QVD?  If so, best would be to add this logic when the QVD is created so that you can keep an optimized load in the user application(s).  If not, then why couldn't you add the logic to the original load?  I'm not understanding why having a massive data model is making you conclude that you shouldn't establish this additional field during that load.

swuehl
MVP
MVP

Well, you would have to reload anyway, so I see no real advantage at the moment, but maybe:

left join (fact) LOAD

[Company Code],

[Profit Centre],

[Company Code] &'-'& [Profit Centre] as PCmap

Resident fact;

Not applicable
Author

Apologies. I just thought that it was not possible to add the field during the Data model load.

I think I may need a little more training on the basics...

johnw
Champion III
Champion III

Ah, OK.  Are you currently loading from a database?  Does your load look something like this?

fact:
SELECT
[Company Code]
,[Profit Centre]
FROM something
WHERE whatever
;

You can add the new field with a preceding load.  Basically, the load statement is implied when you do a select.  The above is functionally equivalent to the below, and I suspect is processed exactly the same internally:

fact:
LOAD *
;
SELECT
[Company Code]
,[Profit Centre]
FROM something
WHERE whatever
;

And that load statement then gives you a place to stick your new field:

fact:
LOAD *
,[Company Code] & ' - ' & [Profit Centre] as PCmap
;
SELECT
[Company Code]
,[Profit Centre]
FROM something
WHERE whatever
;

Of course you might be loading from Excel or who knows what, but the general idea tends to be the same.  There ARE exceptions, like when loading from a QVD like I already mentioned.  You CAN add exactly the same thing to a QVD load, but it can be inefficient from a performance standpoint, so another approach is generally called for with a large data model like yours.  You could also have issues if you were doing a binary load, though we'd solve it about like we would the QVD load, by inserting the new field in the source of the binary load. 

If you gave us specifics, we could probably give you the solution, but I'm guessing that you have enough at this point to experiment and solve it on your own.  Do monitor load times.  If your load time increases by more than a negligible amount, then probably something is wrong, even if you get the right results in the end.

Not applicable
Author

Hi Thanks for this...

I am actually doing a binary load which means I only see a single line in the load script. (this confuses the nube in me)

Since last night my app sponsor has decided that he wants something else from a second table applied too.. I have decided to run with the creation of a third table and map it in from there.

Thank you all for your support!

johnw
Champion III
Champion III

OK, with a binary load, you are correct that you cannot add another field to that specific load.

I think the RIGHT solution is to put the new field in the load inside of the QVW that you are doing the binary load from.  Now we're back to the other cases I was talking about.  And if that QVW is loading from a QVD, then I think the RIGHT solution is to establish the field in the QVW that is building the QVD.

Whatever QVW it's in, I think the right place to establish the field is in the initial load of the rest of the data in that table.

"Right" here is open to interpretation, though.  Perhaps what I should say is something more like, "You will likely get the best overall performance from your system if you put the new field in the initial load of the rest of the data in that table."

But if you can't talk your team into fixing the right QVW ("some guy on a forum said we should" isn't usually a very convincing argument in a professional environment), then you're stuck doing something with lower performance and additional complexity.  In that case, I'd probably go with the left join that Stefan mentioned, which I think is what you ended up doing?  Honestly, that's fine.  It's not what I would do, but it's fine.