topic Re: Condition in back ground scripting in QlikView

Condition in back ground scripting

Tue, 23 Sep 2014 07:46:03 GMT

Hi. Below is an extract of my scripting.

Its basically bringing Sales quantity, Sales Value, Cost of Sales per Item per per Customer per Month.

LOAD

[Sales Qty],
[INCOME],
[COS],

[Financial Year],
[Calendar Month],
[Item Code],
[Customer Code]

FROM

[\\ELL-QVW-001\Qlikview\Qlikview Models\QVD\Pol - ELLDAT\Transactions.qvd]
(qvd)

In our data source, there were certain values which was incorrectly process and therefore i want to correct this in the scripting.

For eg, say, Item Code, ABC, in Calendar month, Jan 2014 , Customer , 'John Smith', the Cost of Sales (COS) was incorrectly processed by an extra $40 000. How do i create a script that decrease the Cost of Sales by $40 000 for this item for that month.

Is this correct? If ([item Code]='ABC' and Calendar Month='Jan 2014' and Customer Code='John Smith', [COS]= -40000)

or do i use a 'where statement' at the end?

Thanks

kind regards

Nayan

Re: Condition in back ground scripting

tresB — Tue, 23 Sep 2014 07:48:53 GMT

Try like:

If ([item Code]='ABC' and Calendar Month='Jan 2014' and Customer Code='John Smith', -40000, [COS] ) as [COS]

Update(Thanking Joe): If you want the value to be reduced by 40000, then try:

If ([item Code]='ABC' and Calendar Month='Jan 2014' and Customer Code='John Smith', [COS]-40000, [COS] ) as [COS]

Re: Condition in back ground scripting

Tue, 23 Sep 2014 07:52:13 GMT

Hi Nayan,

first thing I would say is, you should not be making this change in your QV script, always try to get data fixed at source for you to read in.

Saying that if you absolutely have to do this then the below should work for you. This is assuming you have a single row for the item/month/customer/COS combination

LOAD

[Sales Qty],
[INCOME],
If ([item Code]='ABC' and Calendar Month='Jan 2014' and Customer Code='John Smith', [COS]-40000,[COS]) As [COS],

[Financial Year],
[Calendar Month],
[Item Code],
[Customer Code]

FROM

[\\ELL-QVW-001\Qlikview\Qlikview Models\QVD\Pol - ELLDAT\Transactions.qvd]
(qvd)

hope that helps

Joe

Re: Condition in back ground scripting

Anonymous — Tue, 23 Sep 2014 07:53:37 GMT

you can do it directly for the column

LOAD

[Sales Qty],
[INCOME],

If ([item Code]='ABC' and Calendar Month='Jan 2014' and Customer Code='John Smith', [COS]= -40000, COS) as [COS],

[Financial Year],
[Calendar Month],
[Item Code],
[Customer Code]

FROM

[\\ELL-QVW-001\Qlikview\Qlikview Models\QVD\Pol - ELLDAT\Transactions.qvd]
(qvd)

Re: Condition in back ground scripting

Tue, 23 Sep 2014 07:53:41 GMT

that will replace the COS with -40k rather than reduce won't it?

Re: Condition in back ground scripting

Tue, 23 Sep 2014 07:56:11 GMT

Thank you for the reply. will try it and reload and let you know.

kind regards

Nayan

Re: Condition in back ground scripting

tresB — Tue, 23 Sep 2014 07:57:12 GMT

Yes, now I go through the post entirely(which I don't usually when it's a bit lenghty) , I just saw his line and wanted to reproduce similar.

Re: Condition in back ground scripting

Tue, 23 Sep 2014 07:58:52 GMT

ha no worries, just checking it wasn't my early morning brain playing tricks

Re: Condition in back ground scripting

Tue, 23 Sep 2014 07:59:40 GMT

Hi Joe

I cannot change the data, hence i have to change it in the scripting. Yes, i do have a single row

I will try your scripting changes and let you know the outcome.

Thank you for reply.

kind regards

Nayan

Re: Condition in back ground scripting

Tue, 23 Sep 2014 08:10:41 GMT

haha

Re: Condition in back ground scripting

Tue, 23 Sep 2014 08:23:18 GMT

I get the following error reloading. Which says 'Field Names must be unique within Tables'.

See image attached.

kind regards

Nayan

Re: Condition in back ground scripting

tresB — Tue, 23 Sep 2014 08:26:27 GMT

Comment out the first [COST] field.

Re: Condition in back ground scripting

Tue, 23 Sep 2014 08:29:50 GMT

Hi Tresesco

Thank you for the prompt response. Will try and let you know.

kind regards

Nayan

Re: Condition in back ground scripting

jagan — Tue, 23 Sep 2014 08:32:49 GMT

Hi,

Field names must be unique in the load statement, you have two [COST] fields, so rename any one.

Regards,

jagan.

Re: Condition in back ground scripting

Tue, 23 Sep 2014 09:37:56 GMT

Hi All

Thank you , the formula worked.

kind regards

Nayan