Help with manipulating data and then saving it in 2 different columns

[#1]

Hi, I am new to QlikView and I am in the process of creating my first map and now my first post.

My problem is with coding. What I am trying to achieve is:

Take my source information (SalesTempOut/CostTempOut) and then split and save it as two new fields (Billed Outgoing Value Gross) and (Billed Outgoing Value Cost Gross). When doing this I need to manipulate the values. For -

(Billed Outgoing Value Gross)

If a credit type document i.e the header (Billing Type) = G2 or B3 or Re or S1 multiple by -1 to change the value to a negative value

Else i.e. all other document types (normal invoices) = should clear the value

(Billed Outgoing Value Cost Gross)

If a credit type document i.e the header (Billing Type) = G2 or B3 or Re or S1 leave the value as it is i.e. it will be a positive value

Else i.e. all other document types (normal invoices) = should clear the value

This would then result in me having two columns for credits only - Negative amounts for the Values and positive amounts for the costs. I am then trying to do the same for invoices, ending up with a total of 4 columns for Invoice (Value and Costs) and Credits (Value and Costs)

Extract of my code below does not work - Please help me with the best practice code.

Regards Steve

[VBRP Bill Doc Item]:

LOAD

FKART AS [Billing Type],

WVWR AS [CostTempIn],

WAVWR AS [CostTempOut],

NETWR as [SalesTempIn],

NETWR as [SalesTempOut],

IF ([Billing Type] = 'G2' OR [Billing Type] = 'B3' OR [Billing Type] = 'RE' OR [Billing Type] = 'S1',

[SalesTempIn] = 0,

IF ([Billing Type] <> 'G2' OR [Billing Type] <> 'B3' OR [Billing Type] <> 'RE' OR [Billing Type] <> 'S1',

[SalesTempIn],

[SalesTempIn]))

AS [Billed Incoming Value Gross],

IF ([Billing Type] = 'G2' OR [Billing Type] = 'B3' OR [Billing Type] = 'RE' OR [Billing Type] = 'S1',

[CostTempIn] = 0,

IF ([Billing Type] <> 'G2' OR [Billing Type] <> 'B3' OR [Billing Type] <> 'RE' OR [Billing Type] <> 'S1',

[CostTempIn]*-1,

[CostTempIn]))

AS [Billed Incoming Value Cost Gross],

IF ([Billing Type] = 'G2' OR [Billing Type] = 'B3' OR [Billing Type] = 'RE' OR [Billing Type] = 'S1',

[SalesTempOut]*-1,

IF ([Billing Type] <> 'G2' OR [Billing Type] <> 'B3' OR [Billing Type] <> 'RE' OR [Billing Type] <> 'S1',

[SalesTempOut] = 0,

[SalesTempOut]))

AS [Billed Outgoing Value Gross],

IF ([Billing Type] = 'G2' OR [Billing Type] = 'B3' OR [Billing Type] = 'RE' OR [Billing Type] = 'S1',

[CostTempOut],

IF ([Billing Type] <> 'G2' OR [Billing Type] <> 'B3' OR [Billing Type] <> 'RE' OR [Billing Type] <> 'S1',

[CostTempOut] = 0,

[CostTempOut]))

AS [Billed Outgoing Value Cost Gross]

[#2]

I have done the following for anyone who want this. Also as I am new to this if anyone has a more efficient way of getting the result please update.

IF ([Billing Type] = 'G2' OR [Billing Type] = 'B3' OR [Billing Type] = 'RE' OR [Billing Type] = 'S1',

[SalesTempIn] = ' ',

[SalesTempIn])

AS [Billed Incoming Value Gross],

IF ([Billing Type] = 'G2' OR [Billing Type] = 'B3' OR [Billing Type] = 'RE' OR [Billing Type] = 'S1',

[CostTempIn] = ' ',

[CostTempIn]*-1)

AS [Billed Incoming Value Cost Gross],

IF ([Billing Type] = 'G2' OR [Billing Type] = 'B3' OR [Billing Type] = 'RE' OR [Billing Type] = 'S1',

[SalesTempOut]*-1,

[SalesTempOut] = ' ')

AS [Billed Outgoing Value Gross],

IF ([Billing Type] = 'G2' OR [Billing Type] = 'B3' OR [Billing Type] = 'RE' OR [Billing Type] = 'S1',

[CostTempOut],

[CostTempOut] = ' ')

AS [Billed Outgoing Value Cost Gross]

[d_pranskus]

Hello

It could be done using following syntax

IF(INDEX('|G2|B3|RE|S1|', [Billing Type]) > 0, CostTempOut) AS [Billed Outgoing...]

Function INDEX will search first argument to look if it contains second one, and if it finds, then returns value greater than 0.

Darius

[#4]

Hi Darius

Thanks I appreciate this. I am in the process of replacing my code. One more question please. In the statement you have given me it is looking for document types G2 B3 RE and S1. If I want to turn this code so that it uses all documents that are not G2 B3 RE and S1 - how do I do this? In other words. My old code and the new code you have given me is:

Old

// IF ([Billing Type] = 'G2' OR [Billing Type] = 'B3' OR [Billing Type] = 'RE' OR [Billing Type] = 'S1',
// [CostTempOut],
// [CostTempOut] = ' ')
// AS [Billed Outgoing Value Cost Gross]

New

IF(INDEX('|G2|B3|RE|S1|', [Billing Type]) > 0, CostTempOut) AS [Billed Outgoing Value Cost Gross]

Now I want to do the same for Billed Incoming Value Cost Gross

Old

// IF ([Billing Type] = 'G2' OR [Billing Type] = 'B3' OR [Billing Type] = 'RE' OR [Billing Type] = 'S1',
// [CostTempIn] = ' ',
// [CostTempIn]*-1)
// AS [Billed Incoming Value Cost Gross],

New

?

Many thanks Steve