Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
pls suggest.. i hope this can be done by interval match
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
PFA
Vikas
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
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
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