Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Please help me to write script to load above data and to create output report in Pivot or Straight table
Thanks in advance
PFA
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 ?
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]:
Pno & '-' & Period as PnoAndPeriod,
C_No ,
Claim;
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
