Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
can you share a sample data and the output ?
Dear Taoufiq
Please find the attachment and give the good answer
sagara
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 :
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$);
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;
Output.
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