Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How do I script out the following

I have a sheet called data and a sheet called points.  I want to join the score1, score2, score3, score4 from the data sheet to the points sheet. Take the weight from the point sheet muliple it by the data sheet and sum it up for each company.

9 Replies
arsal_90
Creator III
Creator III

Please find the attached file and let me know is this the desired result

Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

Hi,

CrossTable(Name,Data, 1)

LOAD Comp,

     Score1,

     Score2,

     Score3,

     Score4

FROM

[testdata.xlsx]

(ooxml, embedded labels, table is Data);

Directory;

LOAD Name,

     Weight

FROM

[testdata.xlsx]

(ooxml, embedded labels, table is Weight);

Best,

Aurélien

Help users find answers! Don't forget to mark a solution that worked for you!
rustyfishbones
Master II
Master II

You can use CrossTable.

See the blog post currently available in your Qlik Community home tab

2014-03-26_1505.png

Regards

Alan

Not applicable
Author

Look at the attacched file...

Hope this helps

Not applicable
Author

How do I also have another column that is the sum of all the multiplied scores?

arsal_90
Creator III
Creator III

Can u elaborate your result in excel file. I understand what u need but if you display column wise result then it fesible for me to clearly understand your requirement

Not applicable
Author

Look this new example in the attacchemnt.

Hope this helps.

Not applicable
Author

Try this

 

Test4:

CrossTable(Name,Score,1)
LOAD Comp,
Score1,
Score2,
Score3,
Score4
FROM
C:\testdata.xlsx
(
ooxml, embedded labels, table is Data);

Left Join
LOAD Name,
Weight
FROM
C:\testdata.xlsx
(
ooxml, embedded labels, table is Weight);

Chart Exp:

=SUm(Score)

=Sum(Weight)

=Sum(Score*Weight)

If this is not correct then reply with your required result..

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

With a left join there you run the risk of duplicating rows in the main table with duplicate weights.  I would suggest an ApplyMap, or simply leaving as an association.