How to convert excel formula in Qlik sense

[gp_123]

Hello all, 

I am stuck at one of the excel formula. I want to calculate "Org.Unit" field which has excel formula as "IF([@[Concat Task]]=R6,X6+1,1)".

For eg,
Concat_Task                                               Org.unit     

TS90000168000000000000072929	1
TS90000168000000000000072929	2
TS90000168000000000000072929	3
TS90000168000000000000072929	4
TS90000168000000000000072929	5
TS90000168000000000000073305	1

Is anyone having idea how to Create this excel formula in qlik.
I am providing sample data for the same. Please help me to solve this problem.
Thanks,

[Taoufiq_Zarra]

@gp_123  can you elaborate more the excel formula ?

[HugoRomeira_PT · Accepted solution]

Hello,

My proposal is that you calculate the Org.Unit field in the load script (background).

You should use the peek() function to compare with the previous value, making sure you have your table ordered by Concat Task field.

See solution bellow (loading your excel file):

Open_WF:
LOAD
    Workflow,
    "Range Request #",
    "Workflow Created By",
    Workitem,
    "Workitem Status",
    "WF Opening Date",
    "TS Opening Date",
    "WI Selected",
    "WI Task",
    "WF Form",
    AGI,
    "Class",
    Plant,
    User,
    "Last Name",
    "First Name",
    "WI Text",
    "Concat Task",
    "Responsible for the task",
    Area,
    "Pending For",
    "Red",
    "Green"
FROM [lib://SOURCE FILES/00_General/Open WF.xlsx]
(ooxml, embedded labels, table is Sheet1);




NoConcatenate
Final_Open_WF:
LOAD *,
	if(Peek("Concat Task")="Concat Task",peek(Org.unit)+1,1) as Org.unit /*Simulate a row ID, to count the position of each string grouped by Key, it's very important to keep the order by Key*/

Resident Open_WF

Order by "Concat Task";


DROP Table Open_WF;
Exit script;

Hope it helps.

Best regards

Hugo Romeira

[gp_123]

This is what exactly I am Looking for.
Thanks  Romeira(@HugoRomeira_PT)