Logic help require

[#1]

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.

[#2]

stalwar1‌kush141087‌gwassenaar‌swuehl‌

[vinieme12]

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

[#4]

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

[Gysbert_Wassenaar]

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

[vinieme12]

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);

[MarcoWedel]

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

[Kushal_Chawda]

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))