Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm using the DUAL function to list % Ranges in a table:
=If((((if(([Bid.Rank]>1), [Bid_Amount]))-(if(([Bid.Rank]>1), [Low_Comp_Bid1])))/ (if(([Bid.Rank]>1), [Bid_Amount]))) < 0.01, Dual('0 to 1 %', 1),
If((((if(([Bid.Rank]>1), [Bid_Amount]))-(if(([Bid.Rank]>1), [Low_Comp_Bid1])))/ (if(([Bid.Rank]>1), [Bid_Amount]))) >= 0.01 and (((if(([Bid.Rank]>1), [Bid_Amount]))-(if(([Bid.Rank]>1), [Low_Comp_Bid1])))/ (if(([Bid.Rank]>1), [Bid_Amount]))) < 0.02, Dual('1 to 2 %', 2),
If((((if(([Bid.Rank]>1), [Bid_Amount]))-(if(([Bid.Rank]>1), [Low_Comp_Bid1])))/ (if(([Bid.Rank]>1), [Bid_Amount]))) >= 0.02 and (((if(([Bid.Rank]>1), [Bid_Amount]))-(if(([Bid.Rank]>1), [Low_Comp_Bid1])))/ (if(([Bid.Rank]>1), [Bid_Amount]))) < 0.03, Dual('2 to 3 %', 3),
If((((if(([Bid.Rank]>1), [Bid_Amount]))-(if(([Bid.Rank]>1), [Low_Comp_Bid1])))/ (if(([Bid.Rank]>1), [Bid_Amount]))) >= 0.03 and (((if(([Bid.Rank]>1), [Bid_Amount]))-(if(([Bid.Rank]>1), [Low_Comp_Bid1])))/ (if(([Bid.Rank]>1), [Bid_Amount]))) < 0.04, Dual('3 to 4 %', 4),))))
My problem is that I have duplicate values in one of the other columns (bid_amount) so when I select one of these % ranges, it will show all the ranges that has that duplicate value. I only want it to display the row of the % range selected.
So say there are 2 records that have a bid_amount of 10. But one falls in the 0-1% range and one falls in the 1-2% range. When I select 0-1% i still see the 1-2% range in the table. Hope this makes sense.
Thanks!
Is there a way to do Incremental Load using NON-QVD method?
I have a source table. dw.vr_sample_table. It has columns such as a sample_id int, name varchar and loaddate timestamp.
Is it possible to do using Partial-Reload? Please help to create the script. Thank you
I am using Qlik Sense as a SaaS and trying to create a PixelPerfect Report online (not via Excel Plugin).
But I am struggeling to get LocalDateTimeNow() to display the local date of the user.
My User Profile shows that I am CET plus 1, but the LocalDateTimeNow() always returns CET (Standard time in this case as it is the same).
If this is not working like that, then what does LocalDateTimeNow() actually mean compared to now() ?
GB
Hi there
While loading my table, I would like to establish an subselect on the same data source.
Entry | Timestamp | EventId | PartnerEventId | TimestampPartner | |
2569 | 10.06.2024 00:40:00.00 |
|
15 | ||
2570 | 10.06.2024 00:56:53.53 | 3 | 7 | ||
2571 | 10.06.2024 01:25:00.00 | 12 | 5 | 10.06.2024 00:40:00.00 | |
2572 | 10.06.2024 01:39:33.33 | 3 | 7 | ||
2573 | 10.06.2024 02:15:57.57 | 5 | 15 |
When e.g. loading line 2571 I would like to lookup the TimestampPartner according to PartnerEventId = EventId and having the next older timestamp.
A "classic" SQL statement could look like:
SELECT LIMIT 1
timestamp
FROM table t
WHERE t.timestamp < timestamp AND PartnerId = t.EventId
ORDER BY t.Timestamp DESC
Any suggestions are welcome. Thanks!
Hi All,
I'm working on a project and I created a table using straight table visual, then I created 4 metrics and one field placing the field at the center of the table (third column).
When I display the totals, the metrics correctly calculate it but the label is not shown in the middle column.
¿How can I do this in Qlik Sense?
Thanks,
I have below scenarios where I need to check each ID and see if the HC can fit into the given capacity and if it does, have to merge and if it doesnt leave it as is.
City | ID | Capacity | Headcount | New Headcount |
Dallas | 1 | 100 | 90 | 100 |
Dallas | 2 | 50 | 10 | 0 |
Dallas | 3 | 30 | 30 | 30 |
Ohio | 1 | 50 | 40 | 40 |
Ohio | 2 | 40 | 30 | 30 |
Ex: Dallas has 3 ID's where ID 1 has capacity of 100 and Headcount is 30, here I can merge Headcount from ID 2 to ID 1 and leve ID 3 Headcount as is because if I merge ID 3 Headcount, it will be greater than the capacity of ID 1.
Ex: Ohio - Heacount will remain as is as Capacity is limited and if I merge Headcount will become greater than the given capacity.
How can I achieve this ?
@rwunderlich @Vegar @Digvijay_Singh
Hi,
In this data 'Directorate From' is linked to a separate hierarchy table against a field called Directorate. Directorate is a field users can filter by in the app. I want to create a calculation so that when 'Dir A', for example, is selected, a count of matching values in the 'Directorate To'. However this should only be when 'Directorate From' doesn't equal 'Directorate To'. In the example below, the answer would be 2 (as highlighted) and not 3.
The calculation would go in a straight table.
I hope that makes sense.
Thank you.
Hi,
I have 2 date fields(entry date, exit date), 1 result field, and a policy number.
I need to build the straight table chart in which I should have policy number record with min entry date and max exit date
Eg
Policy Number Entry Date Exit Date Result
1 24/11/2023. 21/10/2024. 100
1 20/12/2022. 21/10/2024. 50
2. 22/01/2020. 02/02/2023. 20
My resultant should be
Policy Number Entry Date Exit Date Result
1 20/12/2022. 21/10/2024. 50
2. 22/01/2020. 02/02/2023. 20
I tried using first sorted to get the date fields but for measure Result i am unable to get the proper values.
please help me on the above
Thanks
Hi, I'm trying to use intervalmatch to connect to tables, one with production, by time and machine and other with shifts.
The production table has a date and a timestamp for each record, the shifts table has the shift ID, start hour, end hour and machine.
Here's the script I'm using
Co_Mov:
//Events:
Load
KEY_LINK_COD_DATA,
KEY_LINK_RECURSO,
Time(Frac(Hora_Fim/60/60)) as [Hora Produção],
Hora_Fim,
Qtd_Movimento_1
From [lib://Teste:DataFiles/Co_Mov_F1.qvd](qvd);
Dt_Turno:
//Shifts
load
KEY_LINK_RECURSO,
maketime(subfield(HIni_T1,':',1),subfield(HIni_T1,':',2),00) as HIni_T1,
maketime(subfield(HIni_T2,':',1),subfield(HIni_T2,':',2),00) as HFim_T1,
maketime(subfield(HIni_T2,':',1),subfield(HIni_T2,':',2),00) as HIni_T2,
maketime(subfield(HIni_T3,':',1),subfield(HIni_T3,':',2),00) as HFim_T2,
maketime(subfield(HIni_T3,':',1),subfield(HIni_T3,':',2),00) as HIni_T3,
maketime(subfield(HIni_T1,':',1),subfield(HIni_T1,':',2),00) as HFim_T3
;
load
//empr_cod,
KEY_LINK_RECURSO,
Time#(tb_alfa1, 'hh:mm:ss') as HIni_T1,
Time#(tb_alfa2, 'hh:mm:ss') as HFim_T1,
Time#(tb_alfa2, 'hh:mm:ss') as HIni_T2,
Time#(tb_alfa3, 'hh:mm:ss') as HFim_T2,
Time#(tb_alfa3, 'hh:mm:ss') as HIni_T3,
Time#(tb_alfa1, 'hh:mm:ss') as HFim_T3
;
load
empr_cod,
empr_cod & '|' & 'M' & '|' & tb_cod as KEY_LINK_RECURSO,
tb_alfa1,
//timestamp(tb_alfa1 + (8/24),'hh:mm') as HrFim,
TB_ALFA2 as tb_alfa2,
tb_alfa3
from [lib://Teste:DataFiles/MULTI_sb_tabvl.qvd](qvd)
WHERE tb_num = 9603;
//exit script;
b:
crosstable(Turno,HoraIni)
load
KEY_LINK_RECURSO,
HIni_T1 as T1,
HIni_T2 as T2,
HIni_T3 as T3
resident Dt_Turno;
//drop table Dt_Turno;
c:
crosstable(Turno,HoraFim)
load
KEY_LINK_RECURSO,
HFim_T1 as T1,
HFim_T2 as T2,
HFim_T3 as T3
resident Dt_Turno;
drop table Dt_Turno;
d:
Load
*,
1 as drop
resident b;
left join(d)
load
*
resident c;
drop table b;
drop table c;
drop field drop;
//exit script;
IntervalMatch:
IntervalMatch ([Hora Produção],KEY_LINK_RECURSO)
Load distinct HoraIni, HoraFim,KEY_LINK_RECURSO resident d;
And the output (please note that the record for the "Hora Produção" = 1:08:24PM should only be associated with Shift T1 but it's associated with all 3 shifts:
In this table I have:
KEY_LINK_RESOURCE = MachineID
Turno = Shift description
HoraIni = shift start time
HoraFim = shift end time
KEY_LINK_COD_DATA = production date
Sum(Qtd_Movimento_1) = produced quantity
Hora Produção = production time
Is it possible that the problem comes from the number formatting?
Thanks in advance.
We are thrilled to announce that the new Qlik Learning will launch on February 17, 2025.
Data Flow simplifies, democratizes, and accelerates the last mile of data preparation for analytics, machine learning, and AI.
From product innovation, to technical tips and tricks, to real world success stories, Qlik Connect offers learning opportunities for all levels.
Read how to embed AI-powered analytics to innovate, enhance customer satisfaction, and fuel business growth.
Your journey awaits! Join us by Logging in and let the adventure begin.
Qlik enhances decision-making with high-speed insights, as Mayborn Group integrates data from various functions across their global operations, gaining a competitive edge in the childcare industry.
Nortera leads agricultural manufacturing analytics and automation with Qlik, reducing short-shipment rates and annual savings in water consumption.
Qlik Data Integration transforms Airbus' aircraft production, leading to over 150 replication tasks and informing more efficient analysis.
Join one of our Location and Language groups. Find one that suits you today!
A private group is for healthcare organizations, partners, and Qlik healthcare staff to collaborate and share insights..
Qlik Communityの日本語のグループです。 Qlik製品に関する日本語資料のダウンロードや質問を日本語で投稿することができます。
Welcome to the group for Brazil users. .All discussions will be in Portuguese.
Hear from your Community team as they tell you about updates to the Qlik Community Platform and more!