Discussion Board for collaboration related to Creating Analytics for QlikView.
I have an Excel File with two sheets.
Sheet1 contains person names and attributes (a person can have 0-n attributes)
Sheet2 contains all available attributes:
I now want to create a table that tells me the attribute distribution. If the attribute is not named for a person, it can be assumed that the person does not have it. The result should look like this:
I already tried a lot with joins and count(total Name) etc., but just can't get it right. Could somebody help me with it?
Thanks a lot
How about this?
LOADNamae,"Attribute"FROM [lib://????/Demo.xlsx](ooxml, embedded labels, table is Tabelle1);
LOADAttributes as "Attribute"FROM [lib://????/Demo.xlsx](ooxml, embedded labels, table is Tabelle2);
Then make a table as below.
Dimension : Attribute
Measure: count(distinct Namae)/count(distinct total Namae)
thanks a lot for this option. Is there also a chance to calculate the proposed table within the Load Script?
Yes you can but it will be a little complicated procedure. First, find the number of Unique Namae and the number of loads per attribute. Finally, create a table with calculating using them.
Tabelle1:LoadNamae,AttributeFROM [lib://downloads/Demo.xlsx](ooxml, embedded labels, table is Tabelle1);
//How many Unique Namae as max RownoCount1:LoadRowNo() as No,Namae,count(Namae) as dummyResident Tabelle1 group by Namae;
Let vNamaeCount = Num(Peek('No', -1, 'Count1')); //Set the last No of Count1 to vNameCount//How many times each attribute is attachedCount2:LoadAttribute,count(Attribute) as AttrCountResident Tabelle1 group by Attribute;
//Make a table with calculationsTabelle2:LOADAttributes as AttributeFROM [lib://downloads/Demo.xlsx](ooxml, embedded labels, table is Tabelle2);
outer join (Tabelle2) LOADAttribute,AttrCount,AttrCount / $(vNamaeCount) as DistributionResident Count2;
//Delete unnecessary tablesdrop tables Count1, Count2;
I hope this will help.