Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Replace the same value in a different row in a table

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



1 Solution

Accepted Solutions
puttemans
Valued Contributor

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

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.

9 Replies
puttemans
Valued Contributor

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

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

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;


talk is cheap, supply exceeds demand
Not applicable

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

It's not work correctly.

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

puttemans
Valued Contributor

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

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.

Not applicable

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

Sorry Johan but it don't works correctly.

anbu1984
Honored Contributor III

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

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

puttemans
Valued Contributor

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

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

Not applicable

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

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

Not applicable

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

Thanks Gysbert for your help !!!

Community Browser