Logic Help

[abhaysingh]

Hey Guys,

Pls find the Sample Data, in this file there are two tabs,

one tab Sheet1 include Data like Fromdate to To Date their item and their ratio,

In second sheet Sheet2, There are Txn Data Sample with Date,Amount, ItemCode.

I want the output like

From Date     | To Date      |     Item     | Value  

Final output in attached sheet2.

thanks in advance..

Attached the new file

[abhaysingh]

pls suggest.. i hope this can be done by interval match

[Kushal_Chawda]

I am not able to open the file i think it is corrupted. But you do something like below

Tabel1:

LOAD date(FROM_DATE+iterno()-1) as Date,

           FROM_DATE

           TO_DATE,

            ITEM

FROM Table1

while FROM_DATE+iterno()-1 <= TO_DATE;

Now link the second table to first table using the date

Table2:

LOAD Date,

          ItemCode,

          Amount

FROM table2

[vikasmahajan]

PFA

Vikas

[abhaysingh]

Hi Vikas,

Output Seems not ok to me,

In this if you see first row, the Date is in between the from date and End Date,

but in Second Raw, the Date in not in range of FromDate and EndDate. this seems wrong to me.

pls suggest

[Kushal_Chawda]

like this

Tabel1:

LOAD *,

           autonumber(ItemCode&Date) as Key;

LOAD date([From Date]+iterno()-1) as Date,

           [From Date]

           [End Date]

           ItemCode

           Ratio

FROM Sheet1

while [From Date]+iterno()-1 <= [End Date];

left join(Tabel1)

LOAD autonumber(Date&ItemCode) as Key,

          ItemCode as ItemCode1,

          Amount

FROM Sheet2;

Now create the Straight Table:

Dimension:

Date

ItemCode1

Expression:

1) Amount- Sum(distinct Amount)

2) Ratio - Sum(distinct Ration)

3) FinalOutput - ([Amount]*[Ratio ])/100

[sunny_talwar]

May be this?

Test1:

LOAD [From Date],

    [End Date],

    ItemCode,

    Ratio

FROM

[TestData Comm.xlsx]

(ooxml, embedded labels, table is Sheet1);

Test2:

LOAD Date,

    Amount,

    ItemCode

FROM

[TestData Comm.xlsx]

(ooxml, embedded labels, table is Sheet2);

Left Join (Test2)

IntervalMatch(Date, ItemCode)

LOAD Distinct

  [From Date],

  [End Date],

  ItemCode

Resident Test1;

Left Join (Test2)

LOAD *

Resident Test1;

DROP Table Test1;

NOTE: Used the sample attached by Vikas and made some minor modifications