SCRIPT

[sagaraperera]

DEAR ALL

SUM ({<ABCD = {'1'}>} (RANGESUM (PROVISION, + OVER_PROVISION))) Here I want the answer to be less than zero for CREDIT and more for DEBIT to be written in SCRIPT

PLEASE HELP

SAGARA

[Taoufiq_Zarra]

can you share a sample data and the output ?

[sagaraperera]

Dear Taoufiq

Please find the attachment and give the good answer 

sagara 

[Taoufiq_Zarra]

if I understood correctly :

LOAD *,
if(Rangesum(MONTHS_PROVISION , UNDER_OVER_PROVISION )>0,Rangesum(MONTHS_PROVISION , UNDER_OVER_PROVISION )) as DEBIT,
if(Rangesum(MONTHS_PROVISION , UNDER_OVER_PROVISION )<0,Rangesum(MONTHS_PROVISION , UNDER_OVER_PROVISION )) as CREDIT 
 INLINE [
    CLAIM_NO,  ACCNOTE ,  MONTHS_PROVISION ,  UNDER_OVER_PROVISION 
    1,  27 ,-25000,15 000
    2,  27 ,20000,-15000
    1,  130 ,-25000,20000
    2,  130 ,20000,35000
];

output :

[sagaraperera]

THANKS YOUR REPLY BUT I WANT ATTACH FORMAT

MY SCRIPT FILE HAS BEEN MASSIVE DATA , BUT I SEND YOU SAMPLE NOW . PLEASE HELP GET A MY REPORT AS REPOST EXE FILE.

SAGARA

MY SCRIPT 

LOAD CLASS_CODE,
MONTHS_PROVISION,
UNDER_OVER_PROVISION
FROM
[C:\SAGA.xlsx]
(ooxml, embedded labels, table is Sheet1);

LOAD GL_CODE,
CLASS_CODE,
GL_DESCRIPTION,
ACCNOTE
FROM
[C:\SA.xls]
(biff, embedded labels, table is Sheet1$);

[Saravanan_Desingh]

One more solution is.

tab1:
LOAD CLASS_CODE, 
     MONTHS_PROVISION, 
     UNDER_OVER_PROVISION, 
     RangeSum(MONTHS_PROVISION, UNDER_OVER_PROVISION) As Value
FROM
[C:\Users\sarav\Downloads\QVwork\SAGA.xlsx]
(ooxml, embedded labels, table is Sheet1);

Right Join(tab1)
LOAD GL_CODE, 
     CLASS_CODE, 
     GL_DESCRIPTION, 
     ACCNOTE
FROM
[C:\Users\sarav\Downloads\QVwork\SA.xls]
(biff, embedded labels, table is Sheet1$);

Gen:
Generic 
LOAD GL_DESCRIPTION, CLASS_CODE, GL_CODE, If(Value>0, 'Debit', 'Credit'), Value
Resident tab1;

Drop Table tab1;

[Saravanan_Desingh]

Output.

[sagaraperera]

Dear 

very thankful your early reply,

Script is ok but my report come as below 

GL_DESCRIPTION	CLASS_CODE	GL_CODE	Debit	Credit
CLAIM PAID/ADMITTED T-C	SA	73210	5,000	5,000
CLAIM PAID/ADMITTED H-H	MS	73160	40,000	40,000
CLAIM PAY PROVI SAHANAYA	SA	45740	5,000	5,000
CLAIM PAYABLE PROVI. MISC	MS	45260	40,000	40,000
Total			90,000	90,000

please give me a expression

Thanks 

saga