populating extra rows in table in script

[lee_williams]

Hi Qlikers

I have a table which I'm trying to populate with extra rows. Basically each code should have 3 colours, Red , Green and Blue and an amount that increases or decreases over time (Period)

Code	Colour	Type	Period	Amount
A1234	Red	X	202101	10
A1234	Red	X	202102	10
A1234	Red	X	202103	15
A1234	Green	X	202101	13
A1234	Green	X	202102	13
A1234	Green	X	202103	10
A2345	Red	X	202101	23
A2345	Red	X	202102	23
A2345	Red	X	202103	23
A2345	Blue	X	202101	21
A2345	Blue	X	202102	21
A2345	Blue	X	202103	21

so it should end up like this , with extra rows that basically have 0 in them as its to used for further calculations down the road.

If i create a new table Using a 'Where Not Exists'  , it doesn't bring back anything because all the colours are present. What i'm trying to do is create a new table which has the ones that don't exist per code and then join that to the original however i don't know how to loop around the 'Code' number. 

so i suppose what I'm looking for is help with the script to create a where not exists clause around the 'Code' column

Code	Colour	Type	Period	Amount
A1234	Red	X	202101	10
A1234	Red	X	202102	10
A1234	Red	X	202103	15
A1234	Green	X	202101	13
A1234	Green	X	202102	13
A1234	Green	X	202103	10
A1234	Blue	X	202101	0
A1234	Blue	X	202102	0
A1234	Blue	X	202103	0
A2345	Red	X	202101	23
A2345	Red	X	202102	23
A2345	Red	X	202103	23
A2345	Blue	X	202101	21
A2345	Blue	X	202102	21
A2345	Blue	X	202103	21
A2345	Green	X	202101	0
A2345	Green	X	202102	0
A2345	Green	X	202103	0

current script :-

ImportTable:
Load
Code,
Colour,
Type,
Amount
from [exceldata.xlsx]
(ooxml, embedded labels, table is data);

Colours:
Load
ColourName
from [colours.xlsx]
(ooxml, embedded labels, table is data);

ExtraColours:
Load
ColourName
Resident Colours where not Exists(Colour, ColourName )
;

Drop table Colours;

Any help please?

[rwunderlich]

Perhaps generate all combinations, then join using a not exists using combined fields. 

// The rows we already have
ImportTable:
LOAD
Code,
     Colour,
     Type,
     Period,
     Amount,
     Hash128(Code, Colour, Type, Period) as Key
FROM
[https://community.qlik.com/t5/App-Development/populating-extra-rows-in-table-in-script/td-p/1878023]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

// Get the key values for the rows we already have, using 0 for value.
NewColours:
LOAD Distinct
Code,
    Type,
    Period,
    0 as Amount
Resident ImportTable   
;
// Generate a row for every color
Join (NewColours) Load * Inline [
Colour
Red
Green
Blue
]
;

// Join any rows that we didn't already have
Outer Join(ImportTable)
LOAD * Resident NewColours
Where not Exists(Key, Hash128(Code, Colour, Type, Period))
;
Drop Table NewColours;
Drop Field Key; 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[lee_williams]

Thanks for the reply  Rob, will look at trying/ implementing today p.s. loved your book, it sits within arms reach as we speak