topic Crosstable saving the file to csv missing Null Values. in QlikView

Crosstable saving the file to csv missing Null Values.

lakshmanvepuri — Sat, 16 Nov 2024 18:51:30 GMT

HI All,

My Scenario to show all the data ( including null values ) while saving / exporting to csv File.

I have multiple columns and rows .. its a a sample mentioned here.

Input file data:

Country	State	Def Value	User Value
INDIA	AP		5555
US	OH	1000
INDIA		2222	88888

OutPut should be :

Country	State	DATATYPE	CONSTANT
INDIA	AP	Def Value	0
INDIA	AP	User Value	5555
US	OH	Def Value	1000
US	OH	User Value	0
INDIA		Def Value	2222
INDIA		User Value	88888

But I am getting the RESULT as:

Country	State	DATATYPE	CONSTANT
INDIA	AP	User Value	5555
INDIA		Def Value	2222
INDIA		User Value	88888
US	OH	Def Value	1000

Enclosed the input File and also qvw file.

Re: Crosstable saving the file to csv missing Null Values.

MayilVahanan — Wed, 01 Apr 2020 06:10:33 GMT

Hi Lakshman,

Try like below

CrossTable(DATATYPE, CONSTANT, 2)
LOAD Country,
State,
if(Len(Trim([Def Value]))=0,0,[Def Value]) as [Def Value],
if(Len(Trim([User Value]))=0,0,[User Value]) as [User Value]
FROM
[Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Re: Crosstable saving the file to csv missing Null Values.

lironbaram — Wed, 01 Apr 2020 06:18:27 GMT

this will work

NullAsValue *;

Set NullValue = 0;

DetailTemp:
LOAD Country,
State,
[Def Value],
[User Value]
FROM
[Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Detail:
CrossTable(DATATYPE, CONSTANT, 2)
load *
resident DetailTemp;

drop Table DetailTemp;

STORE Detail INTO [DETAIL.csv] ( txt, delimiter is ',');

Re: Crosstable saving the file to csv missing Null Values.

lakshmanvepuri — Wed, 01 Apr 2020 07:19:49 GMT

Thank You very much @MayilVahanan and @lironbaram ... Both the Solutions are working...