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

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

Thu, 01 Apr 2010 11:37:35 GMT

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]

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

Fri, 02 Apr 2010 11:11:40 GMT

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]

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

d_pranskus — Fri, 02 Apr 2010 13:00:28 GMT

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

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

Thu, 15 Apr 2010 08:03:51 GMT

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