topic Re: Build the Pivot Table. Incorrect Data in App Development

Build the Pivot Table. Incorrect Data

brabbi1989 — Wed, 24 Apr 2019 09:07:05 GMT

Hi. I need you help. Situation: Client wants some the general report using pivot table for three differents customers and customers apps.

So, I wrote the SQL Stored procedure with all the data for three customers and categories. Data from SQL is correct.

In Qlik Sense I download all the data into one table, after this for customers and customers app I download small table. Qlik builds synt key, which include four columns. When I download data for 1 customer - everything is ok. When I download for 2 customers - the same fields displays in pivot into others format(format in qlik is auto, I prepared and formatted data in SQL, in Qlik data transfers in format).

Why is it happens? Or maybe you know the best idea how I can build it.

Thanks.

Re: Build the Pivot Table. Incorrect Data

miskinmaz — Wed, 24 Apr 2019 09:44:26 GMT

What exactly is the format. Qlik can manage to bring the data into one format either at the script level or expression editor. You can refer the date# or num# function

Re: Build the Pivot Table. Incorrect Data

brabbi1989 — Wed, 24 Apr 2019 09:48:12 GMT

@miskinmaz I convert all to nvarchar type, cause client in one column wants to see money and percent type.

Re: Build the Pivot Table. Incorrect Data

miskinmaz — Wed, 24 Apr 2019 09:49:46 GMT

Can you give some example about how it is stored in sql and how it should be displayed

Re: Build the Pivot Table. Incorrect Data

brabbi1989 — Wed, 24 Apr 2019 10:43:41 GMT

@miskinmaz In SQL it stored as float(123.765), in Qlik it shoul be as $123.765 or percent - 85%. So I transform in Nvarchar and concate with $ or %

Re: Build the Pivot Table. Incorrect Data

miskinmaz — Wed, 24 Apr 2019 10:45:18 GMT

No need to change the format at the sql side. Get the data in plain numbers from the sql and in qlik under expression tab you can manage the format:

Re: Build the Pivot Table. Incorrect Data

brabbi1989 — Wed, 24 Apr 2019 10:48:55 GMT

@miskinmaz

http://prntscr.com/ng72fh

Thanks. What should I do in this case, which on the screen?

Re: Build the Pivot Table. Incorrect Data

miskinmaz — Wed, 24 Apr 2019 10:50:45 GMT

Did you try to change the money expression in the expression tab

Re: Build the Pivot Table. Incorrect Data

brabbi1989 — Wed, 24 Apr 2019 10:54:05 GMT

@miskinmaz Yes, but in one column client wants to have the money and percent value. And I don't know how do it in qlik sense.

Re: Build the Pivot Table. Incorrect Data

miskinmaz — Thu, 25 Apr 2019 07:20:23 GMT

so you can use, the expression at the script level like
num(sales,'$ #00.00') or num(sales,'00.00 %')
You need to use the alt function to get the result likewise. You can attach the sample app

Re: Build the Pivot Table. Incorrect Data

brabbi1989 — Thu, 25 Apr 2019 08:08:06 GMT

@miskinmaz Awesome. Thank you!