Formatting the table

[NiTo]

Hi, 

I need to format the table(attached with the mail) in the following format:

AgrGrp  Sex   Race   Count Percent

Medical condition after race/ethnicity is not required.

I just want to see other fields and there values. Any suggestion?

Thanks

[Mark_Little]

Hi,

Sorry i don't have the time at the moment to work through the full script, but you will want to look at the cross table function. 

Mark

[NiTo]

Hi Mark,

Thanks for the reply. No, cross table will not work here. You need to download the table and see for yourself. I hope you will understand that why the concept of Cross table will not work here.

If it do, please let me know.

Regards,

[canerkan]

Hi NiTo,

this seems like a case for the generic load. 

https://community.qlik.com/t5/Design/The-Generic-Load/ba-p/1473470

This link should give you the information needed.

Let me know if it helped.

Regards

Can

[NiTo]

Hi,

Thanks for your reply and the link that you have provided. 

I did went through the link earlier and it is kind of difficult to understand. Like why to use subfield function or Len function in there???

Thanks

[canerkan]

Hi,

The subfield is generated when splitting your Table. They are basically "marked" so you can trace back what Table they are originating from. E. g.

temp: (<---- Becomes the Subfield of the generated Tables: 'temp.*')

Generic Load

Key,

Attribute,

Value

From...

What happens is that a Table gets generated for each distinct Value in the Field Attribute. Staying with the example of the linked post, the Tables 'Color', 'Diameter', 'Height', etc. are generated and marked with the Subfield 'temp'. E. g. 'temp.Color', 'temp.Diameter', 'temp.Height', etc.

The Subfield 'temp' makes it possible to use For-Next loop to go through all Table names and test them for this subfield to put them in a List variable. 

Set vListOfTables = ;

   For vTableNo = 0 to NoOfTables()

      Let vTableName = TableName($(vTableNo)) ;

      If Subfield(vTableName,'.',1)='temp' Then <-- Testing if the Table belongs to the generic load, hence Subfield 'temp'

         Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ; <-- Put Tablename in List for following Join-Loop

      End If

   Next vTableNo

The Green marked part is to test if the List already has values which means the Length of this List is greater 0. This is important for knowing how to insert the Value. If it is the first Value, there is no need to put a comma in front of the Value. But if it is not the first Value, the comma is needed to separate the Values. That is why the Len() function is inside the If() function.

The List you have generated using this Script can then be used in a For-each-Loop to join the splitted tables back to a single Table. 

You can check the link below for some youtube Tutorials. Maybe this explains it better:

https://www.youtube.com/results?search_query=qlik+generic+load

Regards

Can