Skip to main content

Welcome to
Qlik Community!

cancel
Showing results for 
Search instead for 
Did you mean: 
  • 243,100 members
  • 5,613 online
  • 2,026,289 posts
  • 152,439 Solutions
Announcements
Get Ready. A New Qlik Learning Experience is Coming February 17! LEARN MORE

Welcome to Qlik Community

Recent Discussions

  • forum

    App Development

    Trying to Ignore Duplicate Values

    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.Ra... Show More

    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!

    Show Less
  • forum

    App Development

    Incremental Load (MERGE) using NON-QVD method.

    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 va... Show More

    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

    Show Less
  • forum

    App Development

    Cumulative Balance Calculation

        I have following type of data (attached) for which i need a qlik sense script to be written or if this can be achieved in a visual or both, usin... Show More

     

     

    I have following type of data (attached) for which i need a qlik sense script to be written or if this can be achieved in a visual or both, using set expressions etc.

     



    For Day 1 (First Date) for a particular SKU: Physical Qty + Expected Qty - Forecast = Balance
    For Day 2 and Onwards, for that particular SKU: Physical Qty + Expected Qty + (Previous Date's Balance) - Forecast = Balance

    This means the balance quantity of the previous date for that SKU is carried over to the next date to calculate that day's balance.

    The Qlik script i wrote gives me correct output till day 2/ That is because the balance of Day 1 is not dependent on previous day's balance. The calculations fail thereafter.

    Please help.

    Show Less
  • forum

    Reporting Service & Alerting

    To get LocalTime stamp to work in Qlik SaaS PixelPerfect Report

    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(... Show More

    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

    Show Less
  • forum

    App Development

    Subselect in load script

    Hi there  While loading my table, I would like to establish an subselect on the same data source. Entry Timestamp EventId PartnerEventId TimestampP... Show More

    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
     

     

    5
    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!

     

    Show Less
  • forum

    Qlik Cloud Data Integration

    Total Label for middle Columns

    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 cen... Show More

    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,

    Show Less
  • forum

    App Development

    Check value and add

    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 ... Show More

    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 

    Show Less
  • forum

    App Development

    Count values that match the selected value in an different column

    Hi, In this data 'Directorate From' is linked to a separate hierarchy table against a field called Directorate. Directorate is a field users can filte... Show More

    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.

    Capture.JPG

    I hope that makes sense.

    Thank you.

    Show Less
  • forum

    App Development

    Min and max date records

    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 ... Show More

    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 

     

    Show Less
  • forum

    App Development

    Problem with intervalmatch

    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 d... Show More

    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:

    andrefpc_0-1723031950559.png

    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.

    Show Less
Leaderboard

Customer Story

Accelerating Decision-Making with Qlik Insights

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.

Customer Story

Efficiency in Agriculture: Nortera's Qlik Triumph

Nortera leads agricultural manufacturing analytics and automation with Qlik, reducing short-shipment rates and annual savings in water consumption.

Customer Story

Revolutionizing aircraft production through Data Analytics

Qlik Data Integration transforms Airbus' aircraft production, leading to over 150 replication tasks and informing more efficient analysis.

Location and Language Groups

Choose a Group

Join one of our Location and Language groups. Find one that suits you today!

Collaborate

Healthcare User Group

Healthcare User Group

A private group is for healthcare organizations, partners, and Qlik healthcare staff to collaborate and share insights..

All are welcome

Japan Group

Japan

Qlik Communityの日本語のグループです。 Qlik製品に関する日本語資料のダウンロードや質問を日本語で投稿することができます。

Request to join

Brasil Group

Brazil

Welcome to the group for Brazil users. .All discussions will be in Portuguese.

open to all

Blogs

Community News

Hear from your Community team as they tell you about updates to the Qlik Community Platform and more!