How to add rows dynamically to existing table?

[Qlikuser225]

Hi everyone,

Can anyone please help me in resolving below issue.

Load * inline

[ID Country Value Quater

1   USA       2           Q1

1   CAN       2           Q1

2   SF          2           Q1

2   CAN       2           Q1

3   SF          2           Q2];

I want to achieve below results from above table

[ID Country Value Quater

1   USA       2           Q1

1   CAN       2           Q1

1   SF          0           Q1

2   SF          2           Q1

2   CAN       2           Q1

2  USA         0            Q1

3   SF          2           Q2

3   USA        0           Q2

3  CAN         0           Q2];

Thanks in advance

[stevedark · Accepted solution]

Hi,

If I understand correctly you want to create zero value rows that are populated for missing combinations of values? If that is correct then this code should do that for you. Hopefully the comments make it clear what is going on:

// Put the source in a temp table
tmpAllData:
LOAD
	*
INLINE
[ID,Country,Value,Quarter
1,USA,2,Q1
1,CAN,2,Q1
2,SF,2,Q1
2,CAN,2,Q1
3,SF,2,Q2];


// Get a list of all of one field
tmpCombinedData:
LOAD DISTINCT
	ID
RESIDENT tmpAllData;

// Join to get all combinations of the first two fields
LEFT JOIN (tmpCombinedData)
LOAD DISTINCT
	Country
RESIDENT tmpAllData;

// And again to get all posible combinations
LEFT JOIN (tmpCombinedData)
LOAD DISTINCT
	Quarter
RESIDENT tmpAllData;

// Join the original data back on - missing values will be null
LEFT JOIN (tmpCombinedData)
LOAD
	ID,
	Country,
	Quarter,
	Value
RESIDENT tmpAllData;

DROP TABLE tmpAllData;

// Reload to populate null values with zero
Output:
NOCONCATENATE LOAD
	ID,
	Country,
	Quarter,
	Alt(Value,0) as Value
RESIDENT tmpCombinedData;

// Clean up
DROP TABLE tmpCombinedData;

Hope that helps,

Steve