Create field and assign values

[#1]

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?

[Gysbert_Wassenaar]

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.

[#3]

Thanks Gysbert

Sample data attached

[Gysbert_Wassenaar]

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

[#5]

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

[Gysbert_Wassenaar · Accepted solution]

See attached qvw.

[#7]

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?

[#8]

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;

[tresesco]

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.

[#10]

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