topic Re: Creating a new field based on Load in QlikView

Creating a new field based on Load

Wed, 28 Nov 2012 18:46:50 GMT

I have a working load statement that does fine but I am having a real problem getting a new field created... I am loading RM.CUSTNMBR as field from two different SQL databases which qlickview combines just fine. This field represents our customer numbers and is 4 characters long with the second character identifying which company it belongs to.

For instance:

0675 = COMPANY A (Where the "6" lets me know this

0392 = Company B where the second digit is not a 6

I was hoping to get a new field called "COMPANY" that would populate COMPANY A or COMPANY B in a table based on RM.CUSTNMBR so I could have a combines report/chart that allows the viewer to only show company A or B... I have tried a hundred different ways to do this and am at a loss. If anyone would be able to help me I would very much appreciate it.

Thank you,

Matt

Re: Creating a new field based on Load

MayilVahanan — Wed, 28 Nov 2012 18:54:33 GMT

Try like this

Load *,if(right(Left(RM.CUSTNMBR,2),1)=3,'CompanyB',if(right(Left(RM.CUSTNMBR,2),1)=6,'CompanyA'))

as Company from tablename;

hope it helps

Re: Creating a new field based on Load

IAMDV — Wed, 28 Nov 2012 19:00:58 GMT

Hi Matt,

Not sure if I have fully understood what you want but I'm attaching an example. Please check and let me know if I'm missing something.

It's easy with a mapping load. And you can tag or add the Company Names on the fly without changing the script. And Mapping Load is very fast compared to other methods. Its similar to VLookup in Excel.

I hope this helps!

Cheers,

www.QlikShare.com

Re: Creating a new field based on Load

Wed, 28 Nov 2012 19:18:06 GMT

I think that would work except that I'm loading two different databases with a couple of INNER JOINS which is kind of mucking it up I think.. Here is my entire script (minus the ODBC Connect statement):

I really just want to be able to link a"Company A" or "Company B' to each loaded field based upon the second digit in CUSTNUMBR being a 6 or not... Thanks again for all your help!

SQL

SELECT

RM.CUSTNMBR,

RM.CUSTNAME,

RM.CUSTCLAS,

RM.CPRCSTNM,

RM.CNTCPRSN,

RM.STMTNAME,

RM.SHRTNAME,

RM.ADRSCODE,

RM.UPSZONE,

RM.SHIPMTHD,

RM.TAXSCHID,

RM.SLPRSNID as SLPRSNID,

CM.DOCNUMBR,

CM.CHEKNMBR,

CM.BACHNUMB,

CM.BCHSOURC,

CM.TRXSORCE,

CM.RMDTYPAL,

CM.CSHRCTYP,

CM.CBKIDCRD,

CM.CBKIDCSH,

CM.CBKIDCHK,

CM.DUEDATE,

CM.DOCDATE,

CM.POSTDATE,

CM.PSTUSRID,

CM.GLPOSTDT,

CM.LSTEDTDT,

CM.LSTUSRED,

CM.ORTRXAMT,

CM.CURTRXAM,

CM.SLSAMNT,

CM.SALEDATE,

name.SLPRSNFN + ' ' + name.SPRSNSLN As salesFullName,

dsc.SLTERDSC

FROM IH.dbo.RM20101 CM
INNER JOIN IH.dbo.RM00101 RM ON RM.CUSTNMBR = CM.CUSTNMBR

INNER JOIN IH.dbo.RM00301 name ON name.SLPRSNID = RM.SLPRSNID

INNER JOIN IH.dbo.RM00303 dsc ON dsc.SALSTERR = RM.SALSTERR;

SQL SELECT

                RM.CUSTNMBR,

                RM.CUSTNAME,

                RM.CUSTCLAS,

                RM.CPRCSTNM,

                RM.CNTCPRSN,

                RM.STMTNAME,

                RM.SHRTNAME,

                RM.ADRSCODE,

                RM.UPSZONE,

                RM.SHIPMTHD,

                RM.TAXSCHID,

                RM.SLPRSNID as SLPRSNID,

                CM.DOCNUMBR,

                CM.CHEKNMBR,

                CM.BACHNUMB,

                CM.BCHSOURC,

                CM.TRXSORCE,

                CM.RMDTYPAL,

                CM.CSHRCTYP,

                CM.CBKIDCRD,

                CM.CBKIDCSH,

                CM.CBKIDCHK,

                CM.DUEDATE,

                CM.DOCDATE,

                CM.POSTDATE,

                CM.PSTUSRID,

                CM.GLPOSTDT,

                CM.LSTEDTDT,

                CM.LSTUSRED,

                CM.ORTRXAMT,

                CM.CURTRXAM,

                CM.SLSAMNT,

                 CM.SALEDATE,

                name.SLPRSNFN + ' ' + name.SPRSNSLN As salesFullName,

                 dsc.SLTERDSC



FROM NB.dbo.RM20101 CM

INNER JOIN NB.dbo.RM00101 RM ON RM.CUSTNMBR = CM.CUSTNMBR

INNER JOIN NB.dbo.RM00301 name ON name.SLPRSNID = RM.SLPRSNID

INNER JOIN NB.dbo.RM00303 dsc ON dsc.SALSTERR = RM.SALSTERR;

Re: Creating a new field based on Load

Wed, 28 Nov 2012 19:40:08 GMT

Any way you could look at my load statement above and recomment? I tried the inline load which works succesfully but then it "blows up" due to me not including all the information needed to use you statement... Thanks!

Re: Creating a new field based on Load

Wed, 28 Nov 2012 19:58:09 GMT

I'm sorry about this.. just tring to figure it out and am having trouble.

Your statement above errors out as not being able to find table name...

where I substituted "tablename" as NB.dbo.RM20101 where the RM.CUSTNMBR is

being loaded from... I am probably making a mess of this but if you have

time would you take a look at it.

Thanks

On Wed, Nov 28, 2012 at 1:54 PM, Mayil Vahanan Ramasamy <