Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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,
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;
It's not work correctly.
The field DATA FATTURA where CAUSALE = 'IN' is not equal to the value of CAUSALE = 'FE'.
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.
Sorry Johan but it don't works correctly.
What are join keys for CNSIT and FADET? Is it only [NUMERO FATTURA]?
Could you please specify what exactly you want to change? Do you have a dummy 'result' table you could share?
no, the join keys are [NUMERO FATTURA] and [DATA FATTURA]
Thanks Gysbert for your help !!!