Ask a Question

Welcome to
Qlik Community!

Recent Discussions

Sort By:

App Development

Removing Zero's in a number

Dear guys, I have a sample number of 0000123456000. So how can I get the number only 123456? Please any tips from anyone.

By Prudhvib 2024-05-16 / 10:14 AM

2 1 11
Connectivity & Data Prep

Query Prompt

Can I have a prompt to filter the data when using an SQL query? Something like: Select * from table where column = prompt --- where "prompt" is input... Show More

Can I have a prompt to filter the data when using an SQL query? Something like:

Select * from table where column = prompt

--- where "prompt" is input from the user at run time

Show Less

By Sheri 2024-05-16 / 08:30 AM

6 2 62
Design and Development

tHMap move namespace from elements to root structure in XML

Hi all, How can I move a namespace for an element to the root structure of the XML? Now the xmlns:dt namespace is shown on each and every dt:dt elemen... Show More

Hi all,

How can I move a namespace for an element to the root structure of the XML? Now the xmlns:dt namespace is shown on each and every dt:dt element.

This is what I have:

This is what I want:

Namespace setup:

XML properties

Attribute name:

I couldn't find anything specific in the docs for this. I am sure there must be a straightforward way, but I can't wrap my head around this.

Hopefully someone has an insight on how to achieve this, or has managed to get it working before. Every input is greatly appreciated.

Thanks,
Reno

Show Less

By rlooijmans 2024-05-16 / 09:11 AM

1 2 24
Qlik Application Automation

graphic simple table

Hi , I have these data Product; load ID,annee,etat,Montant from table.qvd ; the result is like this : p1 2019 local 100 p2 2019 ... Show More
Hi ,

I have these data

Product;

load ID,annee,etat,Montant from table.qvd ;

the result is like this :

p1 2019 local 100

p2 2019 local 100

p3 2020 Importé 100

p4 2020 local 100

p5 2021 local 100

i want create a graphic simple table who deplay only the local product , Montant with sélection user
```
the list of local products :
```
Show Less
By Korto2024 2024-05-15 / 07:03 AM

2 2 55
Qlik Replicate

Perfomance overhead on qlik replicate

Dear Team: I have a situation where an update to the table attrep_truncation_safeguard created on source SQL server database ran for 28 days. It was o... Show More

Dear Team:

I have a situation where an update to the table attrep_truncation_safeguard created on source SQL server database ran for 28 days. It was observed that this action created an impact on the tempDB of the SQL server database by filling the disk space which in turn slowed down the database. I would like to understand if an update to the attrep_truncation_safeguard creates overhead performance and how this can be mitigated?

Thank you
Show Less

By ugoaku 2024-05-15 / 10:46 AM

6 2 142
Connectivity & Data Prep

Date Conversion From Numerical Value

How can you convert the numerical date/time value to just the year? I'm uploading data for a historical view of activity and am trying to extract the ... Show More

How can you convert the numerical date/time value to just the year?

I'm uploading data for a historical view of activity and am trying to extract the year from the shipment date.

The shipment date is being imported as the numerical value, instead of the actual date. The excel file has this field in date format. I attempted to remove the time stamp but the time value is apparently still there.

I've tried to convert to date when loading the data with: Year(Date#("SHIP DATE", 'MM/DD/YYYY')) As "Ship Year" but this produces no result. Screen capture below:

Show Less

By AMorella 2024-05-16 / 08:54 AM

2 2 41

Flag to indicate customer switch from one health plan to another

Hi there! I'm attempting to build a flag in the load script that would show whether a member has the same health plan product in current year as they ... Show More

Hi there!

I'm attempting to build a flag in the load script that would show whether a member has the same health plan product in current year as they did in previous year. If they have switched products I want to indicate this with a 'Y.'

See example below:

ID	Year	Product	Switch_Flag
ABC123	2023	Commercial HMO	N
ABC123	2024	Commercial HMO	N
DEF456	2023	Commercial HMO	N
DEF456	2024	Commercial Non-HMO (UBI)	Y
GHI789	2023	Medicare Individual	N
GHI789	2024	Child Health Plus	Y
JKL012	2023	Medicare Individual	N
JKL012	2024	Medicare Individual	N

Any and all help is appreciated! Thanks!

Show Less

App Development

Concatenate / Updating the table

Hi, There are two tables: [results]:Load * Inline[USER_ID, VALUE1, 202, 151, 253, 103, 201, 15];[result_updates]:Load * Inline[USER_ID, VALUE1, 202, 1... Show More
Hi,

There are two tables:
```
[results]:
Load * Inline
[
USER_ID, VALUE
1, 20
2, 15
1, 25
3, 10
3, 20
1, 15
];

[result_updates]:
Load * Inline
[
USER_ID, VALUE
1, 20
2, 15,
1, 25
];
```
They will be concatenated and the new table will look like this::
```
USER_ID, VALUE
1, 20
2, 15
1, 25
3, 10
3, 20
1, 15

1, 20
2, 15,
1, 25
```
However, we want to remove records from the first table that have a user_id for which there are records in the second table. So this is the result we want (order doesn't matter):
```
USER_ID, VALUE
3, 10
3, 20

1, 20
2, 15,
1, 25
```
So, since in the second table (result_updates) there are records with user_id = 1 and 2, then the records from the first table with user_id = 1 and 2 will be removed.

---------------------------------------------------------------------------------------

We tried the following:
```
[result_updates]:
Load * Inline
[
USER_ID, VALUE
1, 20
2, 15,
1, 25
];


Concatenate (result_updates)
Load * Inline
[
USER_ID, VALUE
1, 20
2, 15
1, 25
3, 10
3, 20
1, 15
]
WHERE NOT EXISTS(USER_ID, USER_ID);
```
but the result is:

One record is missing - there is only one record with user id = 3. This is the result we want:
```
USER_ID, VALUE
1, 20
2, 15,
1, 25

3, 10
3, 20
```
Do you have any suggestions on how we can achieve this? 🤔

Show Less
By RoyBatty 2024-05-16 / 09:08 AM

0 2 28
Integration, Extension & APIs

Load Script for REST Connector to Smartsheet

Hello Qlik community. I wanted to share this in the event someone is looking for a comprehensive load script that could be used for the Smartsheet RE... Show More
Hello Qlik community. I wanted to share this in the event someone is looking for a comprehensive load script that could be used for the Smartsheet REST connection.

These are the development issues I came across and built a solution around:
- How do I remove the limit of the first 100 reports/sheets I'm connected to?
- How do I connect to more than 10k records that are limited once a table is found?
- How can I get the script to work on both a Smartsheet 'Sheet' and a Smartsheet 'Report'?
- Is there a way I can pull multiple Smartsheet 'Reports', 'Sheets', or 'Reports and Sheets' all at once?
- What if multiple Smartsheet tables are named the same?
I found some guidance in the forums and then read up on Smartsheet's API documentation to get an understanding of how they structured their API. After working through this, I now have a solution worthy of sharing with everyone. Really hope this helps someone else - Feel free to let me know what you think. And of course, give it a like to show some appreciation 🙂

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I will walk through the solution below and you can download the txt file to paste into your load editor.

The developer only needs to update a few things in the first few rows of code.
- Update the 'Bearer' code
- Update your connection name
- Define the 'Sheet ID'' or 'Report ID' and state if they are a 'Sheet' or 'Report'
Note: Even though we are using the Shartsheet ID for best practice, the Name is what you will see for each table.

From this point forward, everything is set and the only changes to make are optional.
Variables are assigned so that Sheets or Reports can be pulled.

Connection is established and the "include all" query is passed to get more that the first 100 reports/sheets.

There is an optional stop point you can do here where the two [ACTION] comments are. These are so you can run the script at this point and get the list of all Sheets or Reports - This is in the event you wanted to view a list of all tables you are connected to without having to open Smartsheet to to see.

Once we have our list of Reports and Sheets we are going to pull in, a loop will be set to walk through each one and determine how many records there are. This was a necessary step if you wanted to pull in an unlimited number of records, as the row count limiter prevented us from bringing in more than 10k records at once. The "Include All" query only works on the table name table - not at the row level. Also, asking for the row count always returned the row count to the current page you are on (page one by default).

To get around this, I defined each page to be 1 record long with a max page of 9999999 (set whatever limit you want here). Then I could call and ask for how many pages in total there were - Which in essence tells me how many records there are.

It is possible you have 2 or more Smartsheet reports with the same name. There is also a section of code that checks to see if a table name has already been used. If it has, then it renames one of the tables to add the row number of the duplicate from the list of reports you want to bring in. This way it is easy to find and correct if it was a mistake.

After knowing how many records there are, I set variables up to define limits in the query, by calculating how many pages there are, and created a stopping point for a loop to pull in the exact number of pages available for the Report/Sheet.

Define variables:

Loop through each page and pull it in:

The data that comes in will be in an indexed view of the data table. This script will do an index matching process to bring it back to a standard table view. I also left an optional point for you to decide if you want to exclude fields that are fully null and save space, or include them if you need them for later when as there may not be data there yet.

A space is left here if you want to add the table name to each of the fields.
For example: Table.Field1, Table.Field2

After we have all of the pages for the Report/Sheet, we stitch them back together so they become one table again.

Then we close the loop and drop the table after we iterate through the full list of Reports/Sheets we set to include.

At this point the API load is complete and resident loads can be called for any ETL needed.
Show Less
By James_Kirkpatrick 2023-01-06 / 02:56 PM

6 7 1231
App Development

Target date 30 working days from date

Hi, I have several measures where I need to identify if an action happened a specified number of working days after a supplier was instructed. Just ad... Show More

Hi,

I have several measures where I need to identify if an action happened a specified number of working days after a supplier was instructed. Just adding a fixed number of days to a date doesn't take into account the differing lengths of each month. So rather than calculating the number of networkdays between two dates I need to add 30 networkdays to a date to get the target date.

Does anyone have any idea how I might achieve this?

Thanks

Nick
Show Less

By nickking 2024-05-16 / 08:50 AM

0 2 22

High Five

Meet Maxime

Our featured member for May 2024

Leaderboard

igoralcantara

Lech_Miszkiewicz

marksouzacosta

Heinvandenheuvel

marcus_sommer

TomBond77

Popular Resources

View All

Customer Story

Qlik Data Integration & Qlik Replicate story

Qlik enables a frictionless migration to AWS cloud by Empresas SB, a group of Chilean health and beauty retail companies employing 10,000 people with 600 points of sale.