Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Use the Class function, e.g.
If( [Days Late] > 150, 'Above 150', Class([Days Late],10))
HIC
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>
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]
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
Hi,
Try like this in Load statement
LOAD
*,
If( [Days Late] > 150, 'Above 150', Class([Days Late],10)) AS ARBucket
FROM DataSource;
Regards,
Jagan.