Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
abhaysingh
Specialist II
Specialist II

Logic Help

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

6 Replies
abhaysingh
Specialist II
Specialist II
Author

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

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
abhaysingh
Specialist II
Specialist II
Author

Untitled.png

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;


Capture.PNG


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