Formula problem

[#1]

Hello fellow QV users,

I have the following case, see matrix below:

CONTRACT	SUB-CONTRACT	PRODUCTCODE	CORRECT CODE
A	1	mt-10	mt-10
A	2	other	mt-10
A	3	other	mt-10
A	4	other	mt-10
A	5	other	mt-10

I have a contract named A and this contract has 5 subcontracts one of these has a productcode 'MT-10' the other sub-contracts are named 'OTHER'. How can I create the result as shown in column CORRECT CODE (every sub-contract should be named (MT-10). Be aware that there are 25 PRODUCTCODES, each contract has one PRODUCTCODE NAME and the other sub- contracts are also named other.

With kind regards,

Aissam

[d_pranskus]

Hi Aissam

Please find the following code bellow. I hope it helps.

[Data]:
LOAD * INLINE [
CONTRACT, SUBCONTRACT, PRODUCTCODE
A, 1, mt-10
A, 2, other
A, 3, other
A, 4, other
A, 5, other
B, 1, mt-20
B, 2, other
B, 3, other
B, 4, other
B, 5, other
C, 1, mt-30
C, 2, other
C, 3, other
C, 4, other
C, 5, other
];

LEFT JOIN (Data)
LOAD DISTINCT
CONTRACT,
PRODUCTCODE AS CORRECTCODE
RESIDENT [Data]
WHERE SUBCONTRACT = 1;

[spsrk_84]

Hi,

Use the previous and peek functions to create the Correcr Column which is required.Create the column with a condition like if previous Contract Name is similar then copy the same Product Code column to Correct Code.

Regards,

Ajay

[#4]

Hello guys,

The inline function is not a possibility because we have at least 500.000 Contracts :-).

Ajay;

I don't understand the Peek function?

regards,

Aissam

[d_pranskus]

Hi

The inline function was used just to introduce the data, to have something to work with. Please see the second sentence. It finds a correct product code for every contract where subcontract number is 1. If it does not help you please attach some excerpt of your data exported to excel file and I try to figure it out.

Cheers

Darius

[#6]

Hi Darius,

The Contract and subcontract are both unique. Attached you will find an excel file with some data.

Regards,

Aissam

[#7]

hi,

you can use the peek function to create the following code

load

if(subcontract='other',peek(correctcode),subcontract) as correctcode;

from .....;

this should work fine

thanks

[#8]

Hello Tauqueer,

Thanks for your reply. Let's make it a step complicater. We also have contracts that have one subcontract (CONTRACT AND SUBCONTRACT are the same). These contracts have a PRODUCTCODE 'OTHER'. We also have a SUBPRODUCTCODE, in these case it would be 'SPAREPARTS'. I would like to create the following formula.

If the result of forumula (if(subcontract='other',peek(correctcode),subcontract) as correctcode;) = 'other' then look in field SUBPRODUCTCODE. Below I have created a matrix where you can see what I mean. The CORRECT CODE for CONTRACT 09W5000 = SPAREPARTS and not 'OTHER' See matrix below row 5.

CONTRACT	SUB-CONTRACT	PRODUCTCODE	SUBPRODUCTCODE	CORRECT CODE
C09W3610	09W3611	MT-10	MT-10	MT-10
C09W3610	09W3612	OTHER	TANK	MT-10
C09W3610	09W3613	OTHER	SPAREPARTS	MT-10
C09W3610	09W3614	OTHER	HYDROLIC	MT-10
09W5000	09W5000	OTHER	SPAREPARTS	SPAREPARTS

Hope this is clear for you :-),

Regards,

Aissam

[d_pranskus · Accepted solution]

This is my code.

[Data]:

LOAD CONTRACT,

[SUB-CONTRACT],

PRODUCTCODE,

Recno() AS RecNo

FROM [data QV forum question.xls] (biff, embedded labels, table is Sheet1$)

;

[tmp]:

LOAD

CONTRACT,

PRODUCTCODE,

RecNo AS RecNo1

RESIDENT [Data]

WHERE PRODUCTCODE <> 'Other'

ORDER BY RecNo DESC;

LEFT JOIN (tmp)

LOAD

CONTRACT,

PRODUCTCODE,

RecNo1,

IF(CONTRACT = PEEK(CONTRACT), PEEK(RecNo1)-1, 99999999) AS RecNo2

RESIDENT [tmp];

LEFT JOIN ([Data])

INTERVALMATCH (RecNo, CONTRACT)

LOAD RecNo1, RecNo2, CONTRACT RESIDENT tmp;

LEFT JOIN ([Data])

LOAD

CONTRACT,

RecNo1,

RecNo2,

PRODUCTCODE AS CORRECTPRODUCT

RESIDENT tmp;

DROP TABLE tmp;

DROP FIELDS RecNo, RecNo1, RecNo2;

you should modify the code to meet your last requirement just by adding one load statment and the condition

LOAD

CONTRACT,

SUB-CONTRACT,

PRODUCTCODE,

IF(CONTRACT=SUBCONTRACT AND PRODUCTCODE='Other', SUBPRODUCTCODE, CORRECTCODE) AS CORRECTCODE

RESIDENT [Data];

I Hope it helps.

[#10]

hi,

you can then modify your code to work like this....

load

if(productcode='other' and subcontract<>contract,peek(correctcode),if(subcontract=contract,subproductcode,productcode)) as correctcode;

from .....;

this should work fine

thanks