topic Re: Re: Create field and assign values in QlikView

Create field and assign values

Fri, 13 Jun 2014 15:18:24 GMT

I have many rows of transactions and they are categorised by two fields; BOOK and SECTOR. However I need to further categorise each transaction using an extra field called PRODUCT, which is not present in the database.

The product can be inferred by what the SECTOR or BOOK is. For example if the BOOK is 'EURO SSA' i want to assign the a PRODUCT of '€ Agency bonds' or if the SECTOR is CROSSOVER then assign a PRODUCT of 'CDS HY'.

I am not connecting directly to the database, rather our Qlikview developers have given me a raw qvw from which I am loading the data. Therefore my question is, how do I script these rules which specify which PRODUCT values are assigned to each transaction?

Re: Create field and assign values

Gysbert_Wassenaar — Fri, 13 Jun 2014 15:26:53 GMT

You can create an if statement with the logic to determine the product value. But perhaps you can create a mapping table and use the applymap function. Can you post a sample document with some data? See Preparing examples for Upload - Reduction and Data Scrambling for how to do that.

Re: Re: Create field and assign values

Fri, 13 Jun 2014 15:37:16 GMT

Thanks Gysbert

Sample data attached

Re: Create field and assign values

Gysbert_Wassenaar — Fri, 13 Jun 2014 15:49:41 GMT

And do you also have a table with the conditions and the product names?

Re: Re: Create field and assign values

Fri, 13 Jun 2014 15:54:28 GMT

DESK	SECTORS(S)	PRODUCT
DOLLAR SOVS VOL MATCH		$ Agency bonds
SUPRANATIONALS - GSL		$ Agency bonds
EURO SSA		€ Agency bonds
SSA VOL MATCH		€ Agency bonds
BANK CAPITAL - GSL		Bank Capital bonds
CDS EMERGING MKTS		CDS EM
CDS FINANCIALS		CDS Fins
	AIR DEFENSE, AUTO, AUTO PARTS, BUILD STEEL, CHEMICALS, FOOD & BEVERAGE, MEDIA, PAPER MISC, RETAILERS & CONSUMERS, SERVICES, TECHNOLOGY, TELCOS, TOBACCO and UTILITIES	CDS IG
	CROSSOVER and CROSSOVER UPFRONTS	CDS HY
	ITRAXX - FINANCIALS, ITRAXX MAIN/HI-VOL and ITRAXX - XOVER	CDS Index
CDS STRUCTURED PRODUCTS		CDS Options
EM EUROBONDS VOL MATCH		EM Bonds
EMERGING MARKETS EUROBONDS - GSL		EM Bonds
MENA		EM Bonds
MENA BONDS DUBAI		EM Bonds
INDUSTRIALS - GSL		Euro Corporate bonds
EURO FINANCIALS - GSL		Euro Financial bonds
EURO CONVERTIBLE BONDS - GSL		Euro Financial bonds
EUROSTERLING - GSL		Euro Sterling bonds
GBP SSA		Euro Sterling bonds
FRANKFURT TRADES - GSL		Frankfurt
FFT FRN FSA		FRNs
FLOATING RATE NOTES - GSL		FRNs
HIGH YIELD		HY Bonds
HIGH YIELD IGOR		HY Bonds
HIGH YIELD VOL MATCH 1		HY Bonds
NEW ISSUES - GSL		New Issue bonds
WCLK - WCLK		WCLK

Re: Re: Re: Create field and assign values

Gysbert_Wassenaar — Fri, 13 Jun 2014 16:11:53 GMT

See attached qvw.

Re: Re: Re: Create field and assign values

Mon, 16 Jun 2014 07:55:56 GMT

That's great Gysbert. How would I avoid loading the csv file though? Our server admins don't allow the use of excel files so I was hoping to do have the values in the load script itself. Is that possible?

Re: Create field and assign values

Mon, 16 Jun 2014 10:50:48 GMT

I tried the following, but this creates a mess of synthetic tables.

All I need is an extra column in my TRADES table called PRODUCT

ProductMappings:

LOAD * inline

[

DESKS, SECTORS, PRODUCT

DOLLAR SOVS VOL MATCH,,$ Agency bonds

SUPRANATIONALS - GSL,,$ Agency bonds

EURO SSA,,€ Agency bonds

SSA VOL MATCH,,€ Agency bonds

BANK CAPITAL - GSL,,Bank Capital bonds

EM EUROBONDS VOL MATCH,,EM Bonds

EMERGING MARKETS EUROBONDS - GSL,,EM Bonds

MENA,,EM Bonds

MENA BONDS DUBAI,,EM Bonds

INDUSTRIALS - GSL,,Euro Corporate bonds

EURO FINANCIALS - GSL,,Euro Financial bonds

EURO CONVERTIBLE BONDS - GSL,,Euro Financial bonds

EUROSTERLING - GSL,,Euro Sterling bonds

GBP SSA,,Euro Sterling bonds

FRANKFURT TRADES - GSL,,Frankfurt

FFT FRN FSA,,FRNs

FLOATING RATE NOTES - GSL,,FRNs

HIGH YIELD,,HY Bonds

HIGH YIELD IGOR,,HY Bonds

HIGH YIELD VOL MATCH 1,,HY Bonds

NEW ISSUES - GSL,,New Issue bonds

WCLK - WCLK,,WCLK

CDS EMERGING MKTS,,CDS EM

CDS FINANCIALS,,CDS Fins

CDS STRUCTURED PRODUCTS,,CDS Options

CDS SOVEREIGNS,,CDS Sovs

,AIR DEFENSE,CDS IG

,AUTO,CDS IG

,AUTO PARTS,CDS IG

,BUILD STEEL,CDS IG

,CHEMICALS,CDS IG

,FOOD & BEVERAGE,CDS IG

,MEDIA,CDS IG

,PAPER MISC,CDS IG

,RETAILERS & CONSUMERS,CDS IG

,SERVICES,CDS IG

,TECHNOLOGY,CDS IG

,TELCOS,CDS IG

,TOBACCO,CDS IG

,UTILITIES,CDS IG

,LOAN CASH,CDS IG

,CROSSOVER,CDS HY

,CROSSOVER UPFRONTS,CDS HY

,ITRAXX - FINANCIALS,CDS Index

,ITRAXX - FINANCIALS IOI,CDS Index

,ITRAXX MAIN/HI-VOL,CDS Index

,ITRAXX - LEVX,CDS Index

,ITRAXX - SOVX,CDS Index

,ITRAXX - XOVER,CDS Index

];

MapProduct:

Mapping LOAD

DESKS & SECTORS as Key,

PRODUCT

Resident ProductMappings;

Data:

LOAD

ApplyMap('MapProduct',DESK,ApplyMap('MapProduct',SECTOR, Null())) as PRODUCT,

DESK,

SECTOR,

[TRADE DATE],

[TRADE PRIMARY KEY]

Resident TRADES;

Re: Create field and assign values

tresB — Mon, 16 Jun 2014 10:58:11 GMT

At the end of your script use Drop Table command to drop table ProductMappings, like:

....

Drop Table ProductMappings;

You would get rid of synthetic keys.

Re: Re: Create field and assign values

Mon, 16 Jun 2014 11:08:22 GMT

It's still a mess. I've attached a screenshot of what it has come out like and also what I need the solution to be

Re: Create field and assign values

tresB — Mon, 16 Jun 2014 11:14:39 GMT

Then Rename your TRADES table (somewhere in the script you used before this section of script) as Trade_Temp, and then try like:

TRADE:

LOAD

ApplyMap('MapProduct',DESK,ApplyMap('MapProduct',SECTOR, Null())) as PRODUCT

Resident Trade_Temp;

Drop Tables Trade_Temp, Data ;

Re: Create field and assign values

Mon, 16 Jun 2014 11:20:03 GMT

I am doing a Binary load from another qvw and the TRADES table is in that.

Do i need to get the table renamed in the underlying qvw or can I do it in this one?

Re: Create field and assign values

tresB — Mon, 16 Jun 2014 11:23:34 GMT

You can simply rename(in this app itself) like:

Rename table TRADES to Trade_Temp;

Re: Create field and assign values

Mon, 16 Jun 2014 11:41:27 GMT

You guys are geniuses!!!!

That has worked perfectly. Thanks so much for your help