topic Re: How to convert excel formula in Qlik sense in App Development

How to convert excel formula in Qlik sense

gp_123 — Mon, 16 May 2022 10:03:18 GMT

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,

Re: How to convert excel formula in Qlik sense

Taoufiq_Zarra — Mon, 16 May 2022 10:20:00 GMT

@gp_123 can you elaborate more the excel formula ?

Re: How to convert excel formula in Qlik sense

HugoRomeira_PT — Mon, 16 May 2022 10:29:49 GMT

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

Re: How to convert excel formula in Qlik sense

gp_123 — Mon, 16 May 2022 10:51:39 GMT

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