Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
StevenWorm
New Contributor II

Qlik Sense value calculation

Hello qlikers,

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

clipboard_image_0.png

And this is my second excel file:

clipboard_image_3.png

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!

 

Labels (3)
2 Replies
Quy_Nguyen
Contributor III

Re: Qlik Sense value calculation

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
Valued Contributor

Re: Qlik Sense value calculation

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;