Re: Need to Create AR Buckets - Qlik Community

alkesh_sharma · ‎2014-06-10

Hello,

Below is the script which creates AR buckets (Backend SAP)

The buckets created below are 1-30,30-60,60-90,90-120,120-15, and above 120,

But I need buckets as : 1-10,11-20,21-30, .............141-150, above 150.

Below is the script:

// Table: BSAD - Accounting Cleared Items

unqualify *;

[BSID_BSAD]:

Load *,

if([Days Late] <1, 'On Time',

if([Days Late] < 31 and [Days Late] > 0, '1 - 30',

if([Days Late] < 61 and [Days Late] > 30,'31 - 60',

if([Days Late] < 91 and [Days Late] > 60, '61 - 90',

if([Days Late] < 121 and [Days Late] > 90, '91 - 120',

if([Days Late] < 151 and [Days Late] > 120, '121 - 150',

if([Days Late] > 150, 'Over 150', 0))))))) as [Age Bucket],

if([Days Late] <1, [Amount in LC_DMBTR],0) as [On Time],

if([Days Late] < 31 and [Days Late] > 0,[Amount in LC_DMBTR],0) as [1 - 30],

if([Days Late] < 61 and [Days Late] > 30,[Amount in LC_DMBTR],0) as [31 - 60],

if([Days Late] < 91 and [Days Late] > 60,[Amount in LC_DMBTR],0) as [61 - 90],

if([Days Late] < 121 and [Days Late] > 90,[Amount in LC_DMBTR],0) as [91 - 120],

if([Days Late] < 151 and [Days Late] > 120,[Amount in LC_DMBTR],0) as [121 - 150],

if([Days Late] > 150,[Amount in LC_DMBTR],0) as [Over 150];

Load *,

if(([Clearing_AUGDT]>([Due Period Calc] + [Due Date])), (([Amount in LC_DMBTR] + [LC tax_MWSTS])-[Discount amt_SKNTO]),0) as [Net Amt Paid Late],

if(([Clearing_AUGDT]<=([Due Period Calc] + [Due Date])),(([Amount in LC_DMBTR] + [LC tax_MWSTS])-[Discount amt_SKNTO]),0) as [Net Amt Paid On Time],

([Clearing_AUGDT]-([Due Period Calc] + [Due Date])) as [Days Late],

Year([Due Date incl Pay Terms]) as [Due Year incl Pay Terms],

Month([Due Date incl Pay Terms]) as [Due Month incl Pay Terms],

Week([Due Date incl Pay Terms]) as [Due Week incl Pay Terms],

0 as [Days Overdue],

0 as [Overdue Amount incl Pay Terms];

Load

KUNNR as %KNA1_BSID_BSAD_KEY,

BUKRS as %T001_BSID_BSAD_KEY,

UMSKZ &'/' & BSCHL as %BSAD_TBSLT_KEY,

'Cleared' as Cleared_Flag,

if(SHKZG = 'S','D','C') as [Debit/Credit_Flag_SHKZG],

if(SHKZG='H',-1,1) * DMBTR as [Amount in LC_DMBTR],

if(ZBD1T=0 AND ZBD2T=0 AND ZBD3T=0,'0',

if(ZBD1T >0 AND ZBD2T=0 AND ZBD3T=0,'1',

if(ZBD1T<>0 AND ZBD2T<>0 AND ZBD3T=0,'2',

if(ZBD1T=0 AND ZBD2T=0 AND ZBD3T>0,'3',

if(ZBD1T<>0 AND ZBD2T<>0 AND ZBD3T<>0,'4',

if(ZBD1T='-' AND ZBD2T='-' AND ZBD3T='-','','5')))))) as Discount,

if((((ZBD1T + ZBD2T) + ZBD3T) = 0),0,

if((ZBD2T > ZBD3T), (ZBD2T + 1),

if((ZBD1T >= ZBD3T), (ZBD1T + 1), ZBD3T))) as [Due Period Calc],

date(if(SHKZG = 'S',(if(ZFBDT='0000-00-00',BUDAT,ZFBDT)

+(if((((ZBD1T + ZBD2T) + ZBD3T) = 0),0,

if((ZBD2T > ZBD3T), (ZBD2T + 1),

if((ZBD1T >= ZBD3T), (ZBD1T + 1), ZBD3T))))),null())) as [Due Date incl Pay Terms],

if(ZFBDT='0000-00-00',BUDAT,ZFBDT) as [Due Date],

if(SHKZG = 'S',num((AUGDT - BUDAT)),0) as [Days Open],

BLART as [Document type_BLART],

applymap('T003T',BLART) as [Document type Name],

KUNNR as [Customer ID_KUNNR],

BUKRS as [Company Code_BUKRS],

WRBTR as [Amount_WRBTR],

WAERS as [Currency_WAERS],

WAERS as [CurrencyLink],

AUGBL as [Clrng doc._AUGBL],

PRCTR as [Profit Center],

Date(AUGDT,'DD/MM/YYYY') as [Clearing_AUGDT],

month(AUGDT)&' - '&year(AUGDT) as [Clearing_MonthYear],

year(AUGDT) as [Clearing Year],

month(AUGDT) as [Clearing Month],

week(AUGDT) as [Clearing Week],

BELNR as [Document Number_BELNR],

BUDAT as [Posting Date_BUDAT],

GSBER as [Business Area_GSBER],

BLDAT as [Document Date_BLDAT],

Date(BLDAT,'DD/MM/YYYY') as [CurrencyDate],

BSCHL as [Posting key_BSCHL],

BSTAT as [Doc.status_BSTAT],

BUZEI as [Line item_BUZEI],

BUZID as [Line item ID_BUZID],

GJAHR as [Fiscal Year_GJAHR],

MWSKZ as [Tax code_MWSKZ],

WMWST as [Tax amount_WMWST],

MABER as [Dunning Area_MABER],

ZBD1P as [Disc.percent 1_ZBD1P],

ZBD1T as [Days 1_ZBD1T],

ZBD2P as [Disc.percent 2_ZBD2P],

ZBD2T as [Days 2_ZBD2T],

ZBD3T as [Days net_ZBD3T],

ZBFIX as [Fixed_ZBFIX],

date(ZFBDT,'DD/MM/YYYY') as [Baseline date_ZFBDT],

ZLSCH as [Pmt method_ZLSCH],

SGTXT as [Text_SGTXT],

KOSTL as [Cost Center_KOSTL],

UMSKZ as [Special G/L ind_UMSKZ],

HKONT as [G/L_HKONT],

KONTL as [Acct assignment_KONTL],

KONTT as [Acct assgt cat._KONTT],

if(SHKZG='H',-1,1) * MWSTS as [LC tax_MWSTS],

SAKNR as [G/L Account No._SAKNR],

SHKZG as [Debit/Credit_SHKZG],

if(SHKZG='H',-1,1) * SKNTO as [Discount amt_SKNTO]

From $(vTRANSACTION)\BSAD.QVD (qvd) Where ZFBDT > '01/09/2010' AND BUDAT > '2010-09-01' AND KUNNR <> 'C101IB0001' AND KUNNR <> 'C101IC0003' AND KUNNR <> 'C101IS0002';

// Table: BSID - Accounting Open Items

concatenate Load *,

if([Days Overdue] <1, 'Not Due',

if([Days Overdue] < 31 and [Days Overdue] > 0, '1 - 30',

if([Days Overdue] < 61 and [Days Overdue] > 30,'31 - 60',

if([Days Overdue] < 91 and [Days Overdue] > 60, '61 - 90',

if([Days Overdue] < 121 and [Days Overdue] > 90, '91 - 120',

if([Days Overdue] < 151 and [Days Overdue] > 120, '121 - 150',

if([Days Overdue] > 150, 'Over 150', 0))))))) as [Age Bucket],

if([Days Overdue] <1, [Amount in LC_DMBTR],0) as [Current],

if([Days Overdue] < 31 and [Days Overdue] > 0,[Amount in LC_DMBTR],0) as [1 - 30],

if([Days Overdue] < 61 and [Days Overdue] > 30,[Amount in LC_DMBTR],0) as [31 - 60],

if([Days Overdue] < 91 and [Days Overdue] > 60,[Amount in LC_DMBTR],0) as [61 - 90],

if([Days Overdue] < 121 and [Days Overdue] > 90,[Amount in LC_DMBTR],0) as [91 - 120],

if([Days Overdue] < 151 and [Days Overdue] > 120,[Amount in LC_DMBTR],0) as [121 - 150],

if([Days Overdue] > 150,[Amount in LC_DMBTR],0) as [Over 150];

Load *,

0 as [Net Amt Paid Late],

0 as [Net Amt Paid On Time],

0 as [Days Late],

Year([Due Date incl Pay Terms]) as [Due Year incl Pay Terms],

Month([Due Date incl Pay Terms]) as [Due Month incl Pay Terms],

Week([Due Date incl Pay Terms]) as [Due Week incl Pay Terms],

if((today() > [Due Date incl Pay Terms]), (today() - [Due Date incl Pay Terms]), 0) as [Days Overdue],

if([Due Date incl Pay Terms] < today(),[Amount in LC_DMBTR], 0) as [Overdue Amount incl Pay Terms];

Load

KUNNR as %KNA1_BSID_BSAD_KEY,

BUKRS as %T001_BSID_BSAD_KEY,

UMSKZ &'/' & BSCHL as %BSAD_TBSLT_KEY,

'Open' as Cleared_Flag,

if(SHKZG = 'S','D','C') as [Debit/Credit_Flag_SHKZG],

if(SHKZG='H',-1,1) * DMBTR as [Amount in LC_DMBTR],

if(ZBD1T=0 AND ZBD2T=0 AND ZBD3T=0,'0',

if(ZBD1T >0 AND ZBD2T=0 AND ZBD3T=0,'1',

if(ZBD1T<>0 AND ZBD2T<>0 AND ZBD3T=0,'2',

if(ZBD1T=0 AND ZBD2T=0 AND ZBD3T>0,'3',

if(ZBD1T<>0 AND ZBD2T<>0 AND ZBD3T<>0,'4',

if(ZBD1T='-' AND ZBD2T='-' AND ZBD3T='-','','5')))))) as Discount,

if((((ZBD1T + ZBD2T) + ZBD3T) = 0),0,

if((ZBD2T > ZBD3T), (ZBD2T + 1),

if((ZBD1T >= ZBD3T), (ZBD1T + 1), ZBD3T))) as [Due Period Calc],

date(if(SHKZG = 'S',(if(ZFBDT='0000-00-00',BUDAT,ZFBDT)

+(if((((ZBD1T + ZBD2T) + ZBD3T) = 0),0,

if((ZBD2T > ZBD3T), (ZBD2T + 1),

if((ZBD1T >= ZBD3T), (ZBD1T + 1), ZBD3T))))),null())) as [Due Date incl Pay Terms],

if(ZFBDT='0000-00-00',BUDAT,ZFBDT) as [Due Date],

if(SHKZG = 'S',num((today()- BUDAT)),0) as [Days Open],

BLART as [Document type_BLART],

applymap('T003T',BLART) as [Document type Name],

KUNNR as [Customer ID_KUNNR],

BUKRS as [Company Code_BUKRS],

WRBTR as [Amount_WRBTR],

WAERS as [Currency_WAERS],

WAERS as [CurrencyLink],

AUGBL as [Clrng doc._AUGBL],

PRCTR as [Profit Center],

Date(AUGDT,'DD/MM/YYYY') as [Clearing_AUGDT],

month(AUGDT)&' - '& year(AUGDT) as [Clearing_MonthYear],

year(AUGDT) as [Clearing Year],

month(AUGDT) as [Clearing Month],

week(AUGDT) as [Clearing Week],

BELNR as [Document Number_BELNR],

BUDAT as [Posting Date_BUDAT],

GSBER as [Business Area_GSBER],

BLDAT as [Document Date_BLDAT],

Date(BLDAT,'DD/MM/YYYY') as [CurrencyDate],

BSCHL as [Posting key_BSCHL],

BSTAT as [Doc.status_BSTAT],

BUZEI as [Line item_BUZEI],

BUZID as [Line item ID_BUZID],

GJAHR as [Fiscal Year_GJAHR],

MWSKZ as [Tax code_MWSKZ],

WMWST as [Tax amount_WMWST],

MABER as [Dunning Area_MABER],

ZBD1P as [Disc.percent 1_ZBD1P],

ZBD1T as [Days 1_ZBD1T],

ZBD2P as [Disc.percent 2_ZBD2P],

ZBD2T as [Days 2_ZBD2T],

ZBD3T as [Days net_ZBD3T],

ZBFIX as [Fixed_ZBFIX],

Date(ZFBDT,'DD/MM/YYYY') as [Baseline date_ZFBDT],

ZLSCH as [Pmt method_ZLSCH],

SGTXT as [Text_SGTXT],

KOSTL as [Cost Center_KOSTL],

UMSKZ as [Special G/L ind_UMSKZ],

HKONT as [G/L_HKONT],

KONTL as [Acct assignment_KONTL],

KONTT as [Acct assgt cat._KONTT],

if(SHKZG='H',-1,1) * MWSTS as [LC tax_MWSTS],

SAKNR as [G/L Account No._SAKNR],

SHKZG as [Debit/Credit_SHKZG],

if(SHKZG='H',-1,1) * SKNTO as [Discount amt_SKNTO]

From $(vTRANSACTION)\BSID.QVD (qvd) Where ZFBDT > '01/09/2010' AND BUDAT > '2010-09-01' AND KUNNR <> 'C101IB0001' AND KUNNR <> 'C101IC0003' AND KUNNR <> 'C101IS0002';

//Store Data to QVD File:BSID_BSAD.qvd

hic · ‎2014-06-10

Use the Class function, e.g.

If( [Days Late] > 150, 'Above 150', Class([Days Late],10))

HIC

alkesh_sharma · ‎2014-06-10

Hello Henric,

Didn't get you, i am new to qlikview scripting.

Can you write at least 2 - 3 full lines of script?

On Wednesday, June 11, 2014, Henric Cronström <qcwebmaster@qlik.com>

Anonymous · ‎2014-06-10

Below single line may resolve your query. No need to have 2-3 lines of code. CLASS function simply replacing IF ELSE condition from your script. For more information see help.

If([Days Late] > 150, 'Above 150', Class([Days Late],10)) as [Age Bucket]

hic · ‎2014-06-11

The class function just assigns a range to a value. In the above case, if [Days Late] is 87, the class will return

'80 <= [Days Late] < 90'

HIC

jagan · ‎2014-06-11

Hi,

Try like this in Load statement

LOAD

*,

If( [Days Late] > 150, 'Above 150', Class([Days Late],10)) AS ARBucket

FROM DataSource;

Regards,

Jagan.