topic Re: Logic Help in QlikView

Logic Help

abhaysingh — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Logic Help

abhaysingh — Thu, 21 Apr 2016 07:21:16 GMT

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

Re: Logic Help

Kushal_Chawda — Thu, 21 Apr 2016 07:33:10 GMT

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

Re: Logic Help

vikasmahajan — Thu, 21 Apr 2016 08:04:19 GMT

PFA

Vikas

Re: Logic Help

abhaysingh — Thu, 21 Apr 2016 08:19:06 GMT

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

Re: Logic Help

Kushal_Chawda — Thu, 21 Apr 2016 08:27:28 GMT

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

Re: Logic Help

sunny_talwar — Thu, 21 Apr 2016 08:32:03 GMT

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