topic Re: How to use Resident in Inline load script in QlikView

How to use Resident in Inline load script

Wed, 25 Nov 2020 16:16:04 GMT

I have a few columns of data that is just numbers and I want to get the sums before assigning them a name as the dataset currently does not have a name assigned to it.

eg. the table looks something like this

main:

A | B | C | D

1 | 1 | 2 | 3

5 | 1 | 2 | 3

1 | 2 | 2 | 5

7 | 1 | 3 | 3

there are other columns but are omitted in this.

I want to get the sum of one column (eg. A) and then assign the name "category A" to it and same for the rest; and put them all together in a table so it looks something like

Category | total sum

category A | 14

category B | 5

category C | 9

category D | 14

I tried something like:

table:

LOAD * inline [

"Category" | "total sum"

"category A" | sum(A)

"category B" | sum(B)

"category C" | sum(C)

"category D" | sum(D)

resident main] (delimiter is '|');

but it throws a garbage after statement error.

Is it possible to do this ?

Re: How to use Resident in Inline load script

trdandamudi — Thu, 23 Jun 2016 12:13:20 GMT

May be as attached:

Main:

Load

Sum(A) as Category_A,

Sum(B) as Category_B,

Sum(C) as Category_C,

Sum(D) as Category_D;

Load * Inline [

A,B,C,D

1, 1, 2, 3

5, 1, 2, 3

1, 2, 2, 5

7, 1, 3, 3

];

Re: How to use Resident in Inline load script

qlikview979 — Thu, 23 Jun 2016 12:21:45 GMT

Hi, May be try this

T1:

Load * Inline [

A,B,C,D

1, 1, 2, 3

5, 1, 2, 3

1, 2, 2, 5

7, 1, 3, 3

];

T2:

load

sum(A) as Category_A,

sum(B) as Category_B,

sum(C) as Category_C,

sum(D) as Category_D

resident T1;

Re: How to use Resident in Inline load script

Fri, 24 Jun 2016 02:21:22 GMT

I want to show them in a bar chart so that I can see the 4 items together.

This method works but gives me 4 different columns which the bar chart does not take in.

Is there a way such that I can show on the bar chart

- X axis shows category A, B, C ,D (which is the name that I assign)

- Y axis shows the numbers which are the sums.

I should add that those data for A, B, C, D is already loaded through a file so there is no need to inline load the data for A, B, C, D

Re: How to use Resident in Inline load script

Fri, 24 Jun 2016 07:14:03 GMT

I managed to figure out how to do it by referring to these resources;

The Crosstable Load

QlikView Cross Tables

basically, I used a cross table to change the column into a row value.

I had to do some manipulating of data but it managed to achieve what I intended.