Multiple fields to single field selections and calculation

[Hemanth_Qlik]

Hi All,

I have three columns in the table namely sales, From_country,To_Country. Now i have to display country data(single field) on x axis on bar graph and measures as From sales and To sales. Basically I have to club both country fields to single fields but should retain their relationship. Please help if already such solution exists. Also I cannot divide the table into two tables in the datamodel.

Thanks,

Hemanth

[rwunderlich · Accepted solution]

You need to load From_Country and To_Country again as a common field Country and link back to the source row.  Then use Country as DImension.  I'm assuming you have a keyfield named "RecId". If you don't have a key you'll have to generate one using RecNo() when you load the data. 

Countries:
LOAD RecId, From_Country as Country Resident MyData;
Concatenate 
LOAD RecId, To_Country as Country Resident MyData;

-Rob

[Spivey · Accepted solution]

So you want to go from this:

Key	From_Country	To_Country	Sales
1	USA	KOR	5
2	USA	ITA	2
3	USA	FIN	3
4	FIN	ITA	6
5	FIN	KOR	3
6	KOR	USA	6

...to this:

Key	Flow	Country	To Sales	From Sales
1	From_Country	USA		5
2	From_Country	USA		2
3	From_Country	USA		3
4	From_Country	FIN		6
5	From_Country	FIN		3
6	From_Country	KOR		6
6	To_Country	USA	6
3	To_Country	FIN	3
5	To_Country	FIN	3
1	To_Country	KOR	5
2	To_Country	ITA	2
4	To_Country	ITA	6

In order to accomplish this, you'll want to use the CrossTable() function in the load script, like so:

sales:
LOAD RowNo() as Key, * Inline [
From_Country, To_Country, Sales
USA, KOR, 5
USA, ITA, 2
USA, FIN, 3
FIN, ITA, 6
FIN, FIN, 3
KOR, USA, 6
];


sales_load:
CrossTable ([Flow], [Country], 2) LOAD
	Key
  , Sales
  , From_Country
  , To_Country
Resident sales;

Drop Table sales;

That inline table is just a placeholder for your table, though make sure you add a Key field as I have, perhaps using that RowNo() function.

Once you run this script, you'll have something like this:

From here, you can create your bar chart with Country as the dimension, Sum({<Flow={'From_Country'}>} Sales) as the From Sales measure and Sum({<Flow={'To_Country'}>} Sales) as the To Sales measure.

Final product:

[Hemanth_Qlik]

Hi Rwundelich,

Thanks for the help ! I have done the same to achieve the result. however I have also 'from' as direction, 'to' as direction in the link table. And to calculate respective sales of the country , used below expressions.

Dimension: Country

From sales: Sum({<direction = 'from'>}Sales)

To sales: Sum({<direction = 'to'>}Sales)

Regards,

Hemanth.

[Hemanth_Qlik]

Hi Spicey, Thanks for the help !

However, I haven't used cross table to split the rows . I have created a link table with direction as additional column and have used your measures respectively.

Regards,

Hemanth