topic Re: Data Link in QlikView

Data Link

upaliwije — Sat, 02 Aug 2014 15:53:47 GMT

I have following two data tables

Table 1

P_Pno Period Premium

P1 1 1000

P2 1 2000

P3 1 1500

P4 1 4000

P1 2 1200

P2 2 2500

Table 2

Pno Period C_No Claim

P1 1 C_1 100

P1 1 C_2 200

P1 1 C_3 300

P1 2 C-4 100

P1 2 C_5 120

P2 1 C_6 250

P2 1 C_7 150

P2 2 C_8 220

P3 1 C_9 300

From the above Data I want to create a Pivot or Straight Table where the output should be as follows

P_N0 Period Premium C_NO Claim

P1 1 1000 C_1 100

C_2 200

C_3 300

P1 2 1200 C-4 100

C_5 120

P2 1 2000 C_6 250

C_7 150

P2 2 2500 C_8 220

P3 1 1500 C_9 300

P4 1 4000

Please help me to write script to load above data and to create output report in Pivot or Straight table

Thanks in advance

Re: Data Link

maxgro — Sat, 02 Aug 2014 16:20:20 GMT

PFA

Re: Data Link

upaliwije — Sat, 02 Aug 2014 16:28:55 GMT

Thanks for your reply. But I have followng questions

Can I avoid Synthetic Key in the data model ?

Why Premium is treated as a Dimension and not as expression ?

Re: Data Link

maxgro — Sat, 02 Aug 2014 16:53:50 GMT

I used Premium as dim to follow your layout; you can also use as expression

You can remove syn key in different ways, this is one (concatenate the fields)

[Table 1]:

load

P_Pno, Period,

P_Pno & '-' & Period as PnoAndPeriod,

Premium;

load * inline [

P_Pno, Period , Premium

P1 , 1 , 1000

P2 , 1 , 2000

P3 , 1 , 1500

P4 , 1 , 4000

P1 , 2 , 1200

P2 , 2 , 2500

];

[Table 2]:

load

Pno & '-' & Period as PnoAndPeriod,

C_No ,

Claim;

load * inline [

Pno , Period , C_No , Claim

P1 , 1 , C_1, 100

P1 , 1 , C_2 , 200

P1 , 1 , C_3 , 300

P1 , 2 , C-4 , 100

P1 , 2 , C_5 , 120

P2 , 1 , C_6 ,250

P2 , 1 , C_7 , 150

P2 , 2 , C_8 , 220

P3 , 1 , C_9 , 300

];

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys