Qlik Sense value calculation

[Anonymous]

Hello qlikers,

I have to excel Files. This is my first excel file:

And this is my second excel file:

I want to load the second excel file, but in the load I want to calculate a value. 

The formula, is in words:

If the supplier from the first excel file is the same as in the second excel file, then count how often the duration is smaller then the first time and how often the duration is between time1 and time2.
If the supplier from the first excel file is not the same as in the second, check the other supplier

I don't know how can I create a loop. My script looks like:

Supplier:
LOAD *
If( Supplier = Supplier_FE,
(If(Duration <= Duration_FE,true,else)),
else)

Supplier_FE stands for first excel file. I didn't find anything how I can count this in a if formula.

I hope you guys understood me. 

Thank you in advance!

[Quy_Nguyen]

Hope this could help:

FirstExcel:
LOAD * INLINE [
Supplier, Time1, Time2
Fisher, 2, 8
Marshell, 10, 35
Mayer, 1, 20
Morninger, 5, 25
Doesn't exists,1,2
];

SecondExcel:
LOAD * INLINE [
Date, Time, Supplier, Duration
01.01.2019 , 12:43, Fisher, 5.5
31.02.2018, 15:52, Marshell, 15
19.12.2017, 08:12, Mayer, 222.445
18.12.2017, 012:45, Morninger, 2.5
];


Join(FirstExcel) // Join by the same field name in both tables: Supplier
Load Date, Time, Supplier, Duration
Resident SecondExcel;
Drop Table SecondExcel;

_tmp:
Load
	*,
    If(Duration < Time1, 1, 0 ) As Condition1,
    If(Duration >= Time1 and Duration <= Time2, 1,0) As Condition2
Resident FirstExcel;
Drop Table FirstExcel;

Result:
Load Supplier, Sum(Condition1) As Condition1, Sum(Condition2) As Condition2
Resident _tmp
Group by Supplier;
Drop table _tmp;

[ajaykakkar93]

Hi,

Take a look at this also

Data:
load * inline [
Name,Time1,Time2
Fisher,2,8
Marshell,10,35
Mayer,1,20
Morninger,5,25
];
Left join(Data)
Data2:
load * inline [
Date,Time,Name,Duration
01.01.2019,12:43,Fisher,5.5
31.02.2018,15:52,Marshell,15
19.12.2017,08:12:02,Mayer,222.445
18.12.2017,02:45,Morninger,2.5
];

Final1:
Load
  Name,
  Time1,
  Time2,
  Time2 - Time1 as revTime,
  Date,
  Time,
  Duration
Resident Data;

Final:
Load
  Name,
  Time1,
  Time2,
  revTime,
  Date,
  Time,
  Duration,
  If(revTime>=Duration,'Y','N') as ChkTime
Resident Final1;

drop table Data,Final1;

exit script;