topic Re: Create a table from existing table in QlikView

Create a table from existing table

Kohli — Wed, 25 Nov 2020 16:16:04 GMT

Hi team, I have one table:

ID, Name, Sales

1, Hari, 2000

2,hari, 4009

3, Naresh, 5500

4, Naresh, 3000

I want output like these

Name, Sales

Hari, 4009

Naresh, 5500

What is the script for that?

Re: Create a table from existing table

Lisa_P — Mon, 09 Jul 2018 02:51:45 GMT

You can just create a table to show this using Name as dimension and Sum(Sales) as measure.

Qlik is case sensitive, so hari does not match Hari

Re: Create a table from existing table

Kohli — Mon, 09 Jul 2018 02:57:45 GMT

If hari replace with Hari. Can you Please write the Script.

Max Value for Each name in a table.

Re: Create a table from existing table

Anil_Babu_Samineni — Mon, 09 Jul 2018 03:18:16 GMT

Try this?

Sample:

LOAD ID, Replace(Name,'hari','Hari') as Name, Sales Inline [

ID, Name, Sales

1, Hari, 2000

2,hari, 4009

3, Naresh, 5500

4, Naresh, 3000

];

Right Join(Sample)

LOAD Name, Max(Sales) as Sales Resident Sample Group By Name;

Re: Create a table from existing table

kruppas78 — Mon, 09 Jul 2018 06:55:07 GMT

When there are some more wrong written names:

Table:
LOAD UPPER(LEFT(Name,1))&LOWER(Mid(Name,2)) AS Name,
MAX(Sales) AS Sales
RESIDENT RawTable
GROUP BY UPPER(LEFT(Name,1))&LOWER(Mid(Name,2))
;

Re: Create a table from existing table

nishanthi_8 — Mon, 09 Jul 2018 07:03:25 GMT

If the names are not proper in the table and if you want the dimension to be "Name", better go for captialize(Name) and use measure as max(Sales)

Required_Table:

Load

Capitalize(Name) as Name,

Max(Sales) as Sales

Resident Original_Table

Group by Capitalize(Name);