topic Re: SCRIPT in QlikView

SCRIPT

sagaraperera — Sat, 16 Nov 2024 00:22:52 GMT

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

Re: SCRIPT

Taoufiq_Zarra — Fri, 03 Jul 2020 08:24:16 GMT

can you share a sample data and the output ?

Re: SCRIPT

sagaraperera — Fri, 03 Jul 2020 09:02:48 GMT

Dear Taoufiq

Please find the attachment and give the good answer

sagara

Re: SCRIPT

Taoufiq_Zarra — Fri, 03 Jul 2020 09:39:08 GMT

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 :

Re: SCRIPT

sagaraperera — Fri, 03 Jul 2020 11:05:15 GMT

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$);

Re: SCRIPT

Saravanan_Desingh — Sat, 04 Jul 2020 02:08:13 GMT

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;

Re: SCRIPT

Saravanan_Desingh — Sat, 04 Jul 2020 02:09:24 GMT

Output.

Re: SCRIPT

sagaraperera — Mon, 06 Jul 2020 03:37:57 GMT

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