Dataload script - Use Exists in varchar field

ajipaul03 — Fri, 15 Nov 2024 23:30:18 GMT

Does anyone know how to enforce data type while loading?

Here is my scenario , I have two tables CODE_T and VALUE_T the column CODE is varchar 2 on both tables.

CODE_T

CODE	Rank
01	1
02	2
03	1
4	1
5	1

VALUE_T

CODE	DESC	VAL
1	test 1	20
2	test 2	14
3	test 3	65
4	test 4	12
5	test 5	6
6	test 6	12

My script

CODE_T:

LOAD CODE, Rank
FROM [C:\Users\QLIK\TEST_DATA.xlsx](ooxml, embedded labels, table is CODE_T)
where Rank = 1;

VALUE_T

LOAD CODE, DESC, VAL
FROM [C:\Users\QLIK\TEST_DATA.xlsx](ooxml, embedded labels, table is VALUE_T)
where Exists (CODE) ;

What I am looking is to load only 4 & 5 into VALUE_T. Do you know why Qlikview considers '01' is same as '1' ?

Do you know any way we could enforce database's data types?

Thanks,

Aji

Re: Dataload script - Use Exists in varchar field

Nicole-Smith — Mon, 07 Dec 2020 17:53:41 GMT

Since it looks like a num, Qlik assumes that's what it is. If you want to ensure it's treated as text with the 0 in front, you could use the text() function when loading your fields:

CODE_T:
LOAD text(CODE) AS CODE, Rank
FROM [C:\Users\QLIK\TEST_DATA.xlsx](ooxml, embedded labels, table is CODE_T)
where Rank = 1;

VALUE_T:
LOAD text(CODE) AS CODE, DESC, VAL
FROM [C:\Users\QLIK\TEST_DATA.xlsx](ooxml, embedded labels, table is VALUE_T)
where Exists (CODE) ;

topic Re: Dataload script - Use Exists in varchar field in QlikView

Dataload script - Use Exists in varchar field

Re: Dataload script - Use Exists in varchar field