if statement on transform after crosstab: change a value to Zero

Fri, 01 Feb 2013 16:42:43 GMT

i load some data via a cross table, which works fine, i now want a second stage to the transform so that it edits the PYFY and YTD column so that they are filled with 0 for all months except CY12. See examples below for how it looks and how the output i want to look like.

this is the data i am loading

Financial_Line	cross_selling_key	PYFY	YTD3	CY01	CY02	CY03	CY04	CY05	CY06	CY07	CY08	CY09	CY10	CY11	CY12
LTP (Maturity) - Asset	CASH	697.24	1150.44	72.11	77.22	83.13	95.89	98.96	101.71	110.26	105.29	105.25	103.85	97.78	98.99

and i transform using the code

temp:

CrossTable(Date, Amount, 4)

LOAD Financial_Line,

cross_selling_key,

PYFY,

YTD3,

CY01,

CY02,

CY03,

CY04,

CY05,

CY06,

CY07,

CY08,

CY09,

CY10,

CY11,

CY12

FROM

data.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

How it looks

Date	cross_selling_key	Financial_Line	Amount	PYFY	YTD3
CY01	CASH	LTP (Maturity) - Asset	72.11	697.24	1150.44
CY02	CASH	LTP (Maturity) - Asset	77.22	697.24	1150.44
CY03	CASH	LTP (Maturity) - Asset	83.13	697.24	1150.44
CY04	CASH	LTP (Maturity) - Asset	95.89	697.24	1150.44
CY05	CASH	LTP (Maturity) - Asset	98.96	697.24	1150.44
CY06	CASH	LTP (Maturity) - Asset	101.71	697.24	1150.44
CY07	CASH	LTP (Maturity) - Asset	110.26	697.24	1150.44
CY08	CASH	LTP (Maturity) - Asset	105.29	697.24	1150.44
CY09	CASH	LTP (Maturity) - Asset	105.25	697.24	1150.44
CY10	CASH	LTP (Maturity) - Asset	103.85	697.24	1150.44
CY11	CASH	LTP (Maturity) - Asset	97.78	697.24	1150.44
CY12	CASH	LTP (Maturity) - Asset	98.99	697.24	1150.44

after transform i would like my output table to look like thi. I want the data changed in teh transform not set analysis.

Date	cross_selling_key	Financial_Line	Amount	PYFY	YTD3
CY01	CASH	LTP (Maturity) - Asset	72.11	0	0
CY02	CASH	LTP (Maturity) - Asset	77.22	0	0
CY03	CASH	LTP (Maturity) - Asset	83.13	0	0
CY04	CASH	LTP (Maturity) - Asset	95.89	0	0
CY05	CASH	LTP (Maturity) - Asset	98.96	0	0
CY06	CASH	LTP (Maturity) - Asset	101.71	0	0
CY07	CASH	LTP (Maturity) - Asset	110.26	0	0
CY08	CASH	LTP (Maturity) - Asset	105.29	0	0
CY09	CASH	LTP (Maturity) - Asset	105.25	0	0
CY10	CASH	LTP (Maturity) - Asset	103.85	0	0
CY11	CASH	LTP (Maturity) - Asset	97.78	0	0
CY12	CASH	LTP (Maturity) - Asset	98.99	697.24	1150.44

If anyone can help i would be grateful, and if you could post the code in here rather than an example i would be very grateful.

Cheers

Re: if statement on transform after crosstab: change a value to Zero

Mon, 04 Feb 2013 12:29:47 GMT

it is kind a stupid idea but if your data set is small try to make two separate load :

temp:

CrossTable(Date, Amount, 4)

LOAD Financial_Line,

cross_selling_key,

num(0) as PYFY,

num(0) as YTD3,

CY01,

CY02,

CY03,

CY04,

CY05,

CY06,

CY07,

CY08,

CY09,

CY10,

CY11

FROM

(ooxml, embedded labels, table is tab);

CrossTable(Date, Amount, 4)

LOAD Financial_Line,

cross_selling_key,

PYFY,

YTD3,

CY12

FROM

(ooxml, embedded labels, table is tab);

topic Re: if statement on transform after crosstab: change a value to Zero in QlikView

if statement on transform after crosstab: change a value to Zero

Re: if statement on transform after crosstab: change a value to Zero