topic Logic help require in QlikView

Logic help require

Wed, 11 Jan 2017 10:36:48 GMT

Hi All,

I have attached the excel file for logic I need. Please help me to achieve the same. Front end solution would be my first preference, but script solution also I would look.

Re: Logic help require

Wed, 11 Jan 2017 11:21:56 GMT

stalwar1‌kush141087‌gwassenaar‌swuehl‌

Re: Logic help require

vinieme12 — Wed, 11 Jan 2017 11:54:21 GMT

Your explanation in the excel file didn't make any sense to me, can you explain in a bit more detail?

Can you explain row wise operation? or ID wise for ID 2?

For ID 1, check sub ID = ID+2 i.e 3 << which row is this operation referring to ? why ID+2 why not ID+1/3/4???

.. Now for subid 3 << why didn't we check anything for SubID 2??

go back to 1 step back 2 i.e ID= subid 3-1=2 , So pick the row, where ID=2 and Sub ID=3, like wise for all ID, same logic will work

Re: Logic help require

Wed, 11 Jan 2017 12:03:36 GMT

First I need to check, sub id, so for ID=1 (subid= ID+2 i.e 1+2=3).. 2 is constant addition for all ID, for ID =2, need to check sub id (subid=ID+2 i.e 2+2=4). Now I get the subid =3 for ID=1, now we go back to 1 step back from subid to check ID, so ID= subid-1 i.e 3-1=2

Re: Logic help require

Gysbert_Wassenaar — Wed, 11 Jan 2017 16:57:17 GMT

Sorry, I don't understand it at all. Can you write out the entire algorithm as pseudo code?

Re: Logic help require

vinieme12 — Wed, 11 Jan 2017 17:56:39 GMT

Try below

Fact:

LOAD ID,

SubID,

Value,

(ID+1)&'_'&(ID+2) as KEY

FROM

(ooxml, embedded labels, table is Sheet1);

left Join (Fact)

LOAD

(ID)&'_'&(SubID) as KEY,

Value as Value2

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Logic help require

MarcoWedel — Wed, 11 Jan 2017 23:43:53 GMT

Hi,

another solution might be:

tabTest:
LOAD ID,
    SubID,
    Value
FROM [https://community.qlik.com/servlet/JiveServlet/download/1191339-260652/test.xlsx] (ooxml, embedded labels, table is Sheet1, filters(Remove(Row, Pos(Top, 22)),Remove(Row, Pos(Top, 21)),Remove(Row, Pos(Top, 20)),Remove(Row, Pos(Top, 19)),Remove(Row, Pos(Top, 18))));

tabOutput:
Left Keep
LOAD ID-1 as ID,
    Value as OutputValue
Resident tabTest
Where SubID=ID+1;

hope this helps

regards

Marco

Re: Logic help require

Kushal_Chawda — Sun, 22 Jan 2017 10:12:12 GMT

Another option is to use front end expression with data Island table

Data:

LOAD ID,

SubID,

Value

FROM

[test.xlsx]

(ooxml, embedded labels, table is Sheet1);

ID:

LOAD Distinct ID as ID_new

Resident Data;

Create straight table

Dimension:

ID_new

Expression:

=sum(if(ID=ID_new+1 and SubID=ID_new+2, Value))