topic populating extra rows in table in script in App Development

populating extra rows in table in script

lee_williams — Thu, 06 Jan 2022 14:52:42 GMT

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?

Re: populating extra rows in table in script

rwunderlich — Thu, 06 Jan 2022 16:39:44 GMT

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

Re: populating extra rows in table in script

lee_williams — Fri, 07 Jan 2022 09:22:03 GMT

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