Crosstable saving the file to csv missing Null Values.

[lakshmanvepuri]

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.

[MayilVahanan · Accepted solution]

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);

[lironbaram · Accepted solution]

hi 

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 ',');

[lakshmanvepuri]

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