topic Re: Replace the same value in a different row in a table in QlikView

Replace the same value in a different row in a table

Wed, 04 Mar 2015 09:01:17 GMT

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

Re: Replace the same value in a different row in a table

puttemans — Wed, 04 Mar 2015 09:17:37 GMT

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,

Re: Replace the same value in a different row in a table

Gysbert_Wassenaar — Wed, 04 Mar 2015 09:19:19 GMT

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;

Re: Replace the same value in a different row in a table

Wed, 04 Mar 2015 10:24:08 GMT

It's not work correctly.

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

Re: Replace the same value in a different row in a table

puttemans — Wed, 04 Mar 2015 10:35:08 GMT

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.

Re: Replace the same value in a different row in a table

Wed, 04 Mar 2015 10:44:32 GMT

Sorry Johan but it don't works correctly.

Re: Replace the same value in a different row in a table

anbu1984 — Wed, 04 Mar 2015 10:55:45 GMT

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

Re: Replace the same value in a different row in a table

puttemans — Wed, 04 Mar 2015 10:59:11 GMT

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

Re: Replace the same value in a different row in a table

Wed, 04 Mar 2015 11:14:29 GMT

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

Re: Replace the same value in a different row in a table

Thu, 05 Mar 2015 10:39:31 GMT

Thanks Gysbert for your help !!!