Multiple values in a single field

[Apporv_Anand]

I have an excel data as above and want it in second form while loading in Qlik sense  using cross table. How can I achieve the second case where the repeated item values get accumulated in single line irrespective of the number of times the item is repeated.

Can someone please help me 

[JordyWegman]

Hi,

Crosstable is not needed, if you want in done in the script you can do this:

Load
	ID,
	Name,
	Sum( Jan ) as Jan,
	Sum( Feb ) as Feb,
	Sum( Mar ) as Mar	
Group by ID, Name
;
Load * Inline [
ID	, Name	,Jan	, Feb, Mar
1	,A		,5		,10	,
2	,B		,5		,10	,
3	,C		,5		,5	,5
4	,D		,5		,5	,5
5	,E		,		,5	,5
1	,A		,		,	,10
2	,B		,		,	,5
];

If you just need it in the front end, you don't even need to change anything:

Jordy

Climber

[Apporv_Anand]

But if I have a flag value like 'Yes' instead of actual values in cells and using those 'Yes' month values from another table , so how  can I proceed with it . And also considering the empty cells as 'No'  and using those values also.

[JordyWegman]

I'm not really following  you here. Can you provide an example?

Jordy

Climber

[Apporv_Anand]

I have the original data in first format .

So I used crosstable(Month,Flag,2) and a null condition to put 'No' in empty cells.So I achieved second format.But I want something like third format  .

[Kushal_Chawda · Accepted solution]

@Apporv_Anand  try below

Data:
CrossTable(Month,Values,2)
Load * Inline [
ID	, Name	,Jan, Feb, Mar, Apr, May,Jun
1	,A		,Yes,Yes,,,,
2	,B		,Yes
1	,A		,,,,,Yes,Yes
2	,B		,,,Yes,Yes,, ];

Final:
NoConcatenate
 LOAD ID, 
      Name,
      Month,
      if(len(trim(Values))=0,'No',Values) as Values;
LOAD  ID, 
      Name,
      Month,
      maxstring(Values) as Values
Resident Data
Group by ID, 
         Name,
         Month;
              
DROP Table Data;

[Apporv_Anand]

Yeah It worked ...Thank you for the help!