Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
alkesh_sharma
Contributor II

Need to Create AR Buckets

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

5 Replies
Employee
Employee

Re: Need to Create AR Buckets

Use the Class function, e.g.

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

HIC

alkesh_sharma
Contributor II

Re: Need to Create AR Buckets

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>

satyadev_j
Valued Contributor

Re: Need to Create AR Buckets

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]

Employee
Employee

Re: Need to Create AR Buckets

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

MVP
MVP

Re: Need to Create AR Buckets

Hi,

Try like this in Load statement

LOAD

*,

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

FROM DataSource;


Regards,

Jagan.

Community Browser