Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
gidon500
Creator II
Creator II

Promo rebate calculation

Hello All

my bos wants me to calculate the Promo rebate  that we should get

from out supplier .

Attached is an excel file

top left is a table with promo list

promo no , part , dates( start and end period ) , and reabate amount

top right   an invoice list 

the the date , part , qty 

in the middle down is the output i need

i want to create an  object with   list of invoices with the relevant rebate amount for each line ( part )  .

same part cab get diffrenet rebate for each date  , and may not get at all  .

thanks for your help

gidon

1 Solution

Accepted Solutions
Sokkorn
Master
Master

Hi Gidon,

I assume that your data source something like this

1. Table Promo

PromoNoPARTNAME StartDateEndDateUnitRebate
PR0000NWB5UIV1-11-201130-11-2011100
PR0001NWB5SIV1-12-201131-12-2011200
PR0002NWB53IV15-11-201114-12-2011300
PR0003NWB5UIV5-12-201112-12-20111000

2. Table Invoice

InvoiceNo.InvoiceDatePartNumberQuantity
IV10010-11-2011NWB5UIV2
IN10615-11-2011NWB5SIV12
IV10220-11-2011NWB53IV10
IV10502-12-2011NWB5UIV4
IV10105-12-2011NWB5SIV5
IV10310-12-2011NWB5UIV7

Use script below

[Temp]:

LOAD PromoNo,

     PARTNAME,

     StartDate,

     EndDate,

     UnitRebate

FROM [promo-calc1.xlsx]

(ooxml, embedded labels, table is [ promo]);

[tmpMinDate]:

LOAD MIN(DATE([StartDate],'DD-MMM-YYYY'))    AS [MinDate] RESIDENT [Temp];

LET vStartDate = NUM(PEEK('MinDate',0,'tmpMinDate'));

DROP TABLE [tmpMinDate];

// 4 ------------------ Param End Date for create calendar -------------------------------------------

[tmpMaxDate]:

LOAD MAX(DATE([EndDate],'DD-MMM-YYYY'))    AS [MaxDate] RESIDENT [Temp];

LET vEndDate = NUM(PEEK('MaxDate',0,'tmpMaxDate'));

DROP TABLE [tmpMaxDate];

FOR i=vStartDate TO vEndDate

    [DateList]:

    LOAD DATE($(i)) AS [DateList]

    AutoGenerate 1;

NEXT

//*********** Clear Variable ***********

LET vStartDate = NULL();

LET vEndDate = NULL();

LET i = NULL();

//**************************************

[intTable]:

IntervalMatch (DateList)

LOAD

    DATE([StartDate])    AS [tmpStartDate],

    [EndDate]        AS [tmpEndDate]

RESIDENT [Temp];

LEFT JOIN ([intTable])

LOAD

    [StartDate]        AS [tmpStartDate],

    [EndDate]    AS [tmpEndDate],

    PromoNo,

    PARTNAME, 

    UnitRebate

RESIDENT [Temp];

DROP TABLE [DateList];

DROP TABLE [Temp];

[Promo]:

LOAD DISTINCT

    TRIM(PromoNo)    AS [PromoNo],

    TRIM(PARTNAME)    AS [PARTNAME],

    DATE([DateList],'DD-MM-YYYY')    AS [PromoDate],

    UnitRebate

RESIDENT [intTable];

DROP TABLE [intTable];

//==============

[tmpPromo]:

MAPPING

LOAD

     PARTNAME &'-'&PromoDate,

     UnitRebate

RESIDENT [Promo];

[Inv]:

LOAD InvoiceNo.,

     InvoiceDate,

     PartNumber,

     Quantity,

     APPLYMAP('tmpPromo',TRIM(PartNumber)&'-'&DATE(InvoiceDate,'DD-MM-YYYY'),0)    AS UnitRebate

FROM

[promo-calc1.xlsx]

(ooxml, embedded labels, table is [ Invoices]);

DROP TABLE [Promo];

See attached files also.

HTH and let me know.

Regards,

Sokkorn Cheav

View solution in original post

2 Replies
Sokkorn
Master
Master

Hi Gidon,

I assume that your data source something like this

1. Table Promo

PromoNoPARTNAME StartDateEndDateUnitRebate
PR0000NWB5UIV1-11-201130-11-2011100
PR0001NWB5SIV1-12-201131-12-2011200
PR0002NWB53IV15-11-201114-12-2011300
PR0003NWB5UIV5-12-201112-12-20111000

2. Table Invoice

InvoiceNo.InvoiceDatePartNumberQuantity
IV10010-11-2011NWB5UIV2
IN10615-11-2011NWB5SIV12
IV10220-11-2011NWB53IV10
IV10502-12-2011NWB5UIV4
IV10105-12-2011NWB5SIV5
IV10310-12-2011NWB5UIV7

Use script below

[Temp]:

LOAD PromoNo,

     PARTNAME,

     StartDate,

     EndDate,

     UnitRebate

FROM [promo-calc1.xlsx]

(ooxml, embedded labels, table is [ promo]);

[tmpMinDate]:

LOAD MIN(DATE([StartDate],'DD-MMM-YYYY'))    AS [MinDate] RESIDENT [Temp];

LET vStartDate = NUM(PEEK('MinDate',0,'tmpMinDate'));

DROP TABLE [tmpMinDate];

// 4 ------------------ Param End Date for create calendar -------------------------------------------

[tmpMaxDate]:

LOAD MAX(DATE([EndDate],'DD-MMM-YYYY'))    AS [MaxDate] RESIDENT [Temp];

LET vEndDate = NUM(PEEK('MaxDate',0,'tmpMaxDate'));

DROP TABLE [tmpMaxDate];

FOR i=vStartDate TO vEndDate

    [DateList]:

    LOAD DATE($(i)) AS [DateList]

    AutoGenerate 1;

NEXT

//*********** Clear Variable ***********

LET vStartDate = NULL();

LET vEndDate = NULL();

LET i = NULL();

//**************************************

[intTable]:

IntervalMatch (DateList)

LOAD

    DATE([StartDate])    AS [tmpStartDate],

    [EndDate]        AS [tmpEndDate]

RESIDENT [Temp];

LEFT JOIN ([intTable])

LOAD

    [StartDate]        AS [tmpStartDate],

    [EndDate]    AS [tmpEndDate],

    PromoNo,

    PARTNAME, 

    UnitRebate

RESIDENT [Temp];

DROP TABLE [DateList];

DROP TABLE [Temp];

[Promo]:

LOAD DISTINCT

    TRIM(PromoNo)    AS [PromoNo],

    TRIM(PARTNAME)    AS [PARTNAME],

    DATE([DateList],'DD-MM-YYYY')    AS [PromoDate],

    UnitRebate

RESIDENT [intTable];

DROP TABLE [intTable];

//==============

[tmpPromo]:

MAPPING

LOAD

     PARTNAME &'-'&PromoDate,

     UnitRebate

RESIDENT [Promo];

[Inv]:

LOAD InvoiceNo.,

     InvoiceDate,

     PartNumber,

     Quantity,

     APPLYMAP('tmpPromo',TRIM(PartNumber)&'-'&DATE(InvoiceDate,'DD-MM-YYYY'),0)    AS UnitRebate

FROM

[promo-calc1.xlsx]

(ooxml, embedded labels, table is [ Invoices]);

DROP TABLE [Promo];

See attached files also.

HTH and let me know.

Regards,

Sokkorn Cheav

gidon500
Creator II
Creator II
Author

Hello  Sokkorn ,

it works great  ,I understood  your steps  .

I will put it now in my main script .

and check it

thank you very much for your help It works

Gidon