topic Re: Looping through SQL data in QlikView

Looping through SQL data

jasonwills — Sat, 16 Nov 2024 03:01:19 GMT

Hi All,

after some help here, as i'm not sure where to start - and i hope i explain it properly too 😉

I have a SQL data source, and it represents a tree style structure, something like:

ID	SerialNumber	ParentSerialNumber	Name

1	12345	4444	Test 1
2	67892	3333	Test 2
3	77645	4444	Test 3
4	4444		Parent 1
5	3333		Parent 2

So, looking at the sample data above there are two "parents" with 1 having 2 sub entries and the second only having 1.

Hopefully that is the structure explained OK.

I can import the data OK, however i am wanting to do other things with the data, So, my question is, what syntax do i need to loop through each record that has a "ParentSerialNumber" of 4444 for instance.

Thanks for any help

Jason

Re: Looping through SQL data

jasonwills — Fri, 12 Jul 2019 20:50:57 GMT

Hi All, just wanting to expand a little to help explain.

i have a SQL table - i am importing it through the script as so:

LoadIntoList:

Sql SELECT
t.SerialNumber as SerialNumber
FROM
tableName as t
WHERE t.ParentSerialNumber= '4444';

So this will give me a list of all records who have a ParentSerialNumber of 4444.

What i'd then like to do is to run another SQL statement to import other records from a different table, but looping through and using each 'SerialNumber' obtained from the previous statement as a variable.

Cheers

Jason

Re: Looping through SQL data

Nadi_Clarke — Mon, 15 Jul 2019 18:28:47 GMT

Have you used HierarchyBelongsTo for all hierarhy in Qlik already?

1) temp1:

HierarchyBelongsTo (NodeID, AncestorID, NodeName, AncestorID, AncestorName, DepthDiff)

LOAD * LoadIntoList

2) Leave only ISNULL(AncestorName) = 1

3) for each i in FieldValueList (NodeID)

Re: Looping through SQL data

jasonwills — Tue, 16 Jul 2019 07:55:20 GMT

hi @Nadi_Clarke ,

i wasn't even aware of this - thanks for the pointers, i will investigate and have a look.

Regards

Jason