Replace the same value in a different row in a table

[#1]

Good morning,

I have the following problem:

In the table the field value DATA FATTURA is correct only where the value of the field CAUSALE is FE.

How can I replicate this value in all areas where the field CAUSALE is different to FE?

Data is retrieved from two different tables whose field of union is DATA FATTURA and NUMERO FATTURA..

CNSIT:

    LOAD

        Num(CNNUFA,000000000) as [NUMERO FATTURA],

        CNCAUS as [CAUSALE],

        date#(CNDARS & CNDARA &'-'& CNDARM &'-'& CNDARG, 'YYYY-MM-DD'), 'DD-MM-YYYY') as [DATA FATTURA],

        date (date#(CNDTVI,'YYYYMMDD'),'DD-MM-YYYY') as [DATA INCASSO],

    FROM

...............

(qvd);

FADET:

LOAD

    date (date#(DFDFA,'YYYYMMDD'),'DD-MM-YYYY') as    [DATA FATTURA],

    Num(DFNFA,000000000)    as    [NUMERO FATTURA],

...............

(qvd);

Thanks.

Giovanni

[puttemans]

Hello Giovanni,

I'd create a new variable with the IF-clause in a preceding load. (Don't know what exactly you want to do with it, so gave it a 1 (true) and 0 (false) value)

CNSIT:

     LOAD *,

          If (Causale <> 'FE', 1,0) as [DATA FATTURA INCORRECT];

    LOAD

        Num(CNNUFA,000000000) as [NUMERO FATTURA],

        CNCAUS as [CAUSALE],

        date#(CNDARS & CNDARA &'-'& CNDARM &'-'& CNDARG, 'YYYY-MM-DD'), 'DD-MM-YYYY') as [DATA FATTURA],

        date (date#(CNDTVI,'YYYYMMDD'),'DD-MM-YYYY') as [DATA INCASSO],

    FROM

As an aside, I'd bring in the invoice number in the CNSIT data table as well through a mapping.

Regards,

[Gysbert_Wassenaar]

Maybe like this:

TEMP:
    LOAD
        Num(CNNUFA,000000000) as [NUMERO FATTURA],
        CNCAUS as [CAUSALE],
        date#(CNDARS & CNDARA &'-'& CNDARM &'-'& CNDARG, 'YYYY-MM-DD'), 'DD-MM-YYYY') as [DATA FATTURA],
        date (date#(CNDTVI,'YYYYMMDD'),'DD-MM-YYYY') as [DATA INCASSO]
    FROM ............... (qvd);

CNSIT:
    LOAD
        [NUMERO FATTURA],
        [CAUSALE],
        [DATA INCASSO],
        IF( [CAUSALE]='FE', [DATA INCASSO], peek([DATA FATTURA])) as [DATA FATTURA]
    RESIDENT TEMP
    ORDER BY [NUMERO FATTURA], [CAUSALE]
;

DROP TABLE TEMP;

[#4]

It's not work correctly.

The field DATA FATTURA where CAUSALE = 'IN' is not equal to the value of CAUSALE = 'FE'.

[puttemans · Accepted solution]

Hi Giovanni,

What exactly do you want to change? Do you want to change the incorrect invoice date per invoice number with the correct one?

If so, then you could use a mapping table.

After your load, you add :

Map_date:

     Mapping load

          [NUMERO FATTURA],

           [DATA FATTURA]

RESIDENT CNSIT

WHERE [CAUSALE] = 'FE';

CNSIT2:

    LOAD *,

          APPLYMAP('Map_date', [NUMERO FATTURA], '-') as  [DATA FATTURA NEW]

  RESIDENT CNSIT;

  DROP TABLE CNSIT;     

This will only work if you are sure to only have 1 correct invoice date per invoice number.

[#6]

Sorry Johan but it don't works correctly.

[anbu1984]

What are join keys for CNSIT and FADET? Is it only [NUMERO FATTURA]?

[puttemans]

Could you please specify what exactly you want to change? Do you have a dummy 'result' table you could share?

[#9]

no, the join keys are [NUMERO FATTURA] and [DATA FATTURA]

[#10]

Thanks Gysbert for your help !!!