Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

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

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_Linecross_selling_keyPYFYYTD3CY01CY02CY03CY04CY05CY06CY07CY08CY09CY10CY11CY12
LTP (Maturity) - AssetCASH697.241150.4472.1177.2283.1395.8998.96101.71110.26105.29105.25103.8597.7898.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  
CY01CASHLTP (Maturity) - Asset72.11697.241150.44 
CY02CASHLTP (Maturity) - Asset77.22697.241150.44 
CY03CASHLTP (Maturity) - Asset83.13697.241150.44 
CY04CASHLTP (Maturity) - Asset95.89697.241150.44 
CY05CASHLTP (Maturity) - Asset98.96697.241150.44 
CY06CASHLTP (Maturity) - Asset101.71697.241150.44 
CY07CASHLTP (Maturity) - Asset110.26697.241150.44 
CY08CASHLTP (Maturity) - Asset105.29697.241150.44 
CY09CASHLTP (Maturity) - Asset105.25697.241150.44 
CY10CASHLTP (Maturity) - Asset103.85697.241150.44 
CY11CASHLTP (Maturity) - Asset97.78697.241150.44 
CY12CASHLTP (Maturity) - Asset98.99697.241150.44

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

Datecross_selling_keyFinancial_LineAmountPYFYYTD3
CY01CASHLTP (Maturity) - Asset72.1100
CY02CASHLTP (Maturity) - Asset77.2200
CY03CASHLTP (Maturity) - Asset83.1300
CY04CASHLTP (Maturity) - Asset95.8900
CY05CASHLTP (Maturity) - Asset98.9600
CY06CASHLTP (Maturity) - Asset101.7100
CY07CASHLTP (Maturity) - Asset110.2600
CY08CASHLTP (Maturity) - Asset105.2900
CY09CASHLTP (Maturity) - Asset105.2500
CY10CASHLTP (Maturity) - Asset103.8500
CY11CASHLTP (Maturity) - Asset97.7800
CY12CASHLTP (Maturity) - Asset98.99697.241150.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

1 Reply
Not applicable

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

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);