Set a dummy value to fields if not present in the source

[imhappiee]

Hi,

Hope this find the right person who can help me out

Source data: Text files which are generated automatically and can vary from 10-15 in number  and will be dropped in to folder

Qlik View Application: We have created an application based on a standard source which contains set of 11 fields and relevant expressions based on this 11 fields in the front end , so the application is freezed

Issue : some times the source may not contain all the 11 fields and the data can have 8-9 fields,In that case I don't want to change the code in the Qlik View application and set the non available field values as 0

Ex: standard source data

Code	emp	sale	qty	sale type	model	Region	country	state	reporting	Month
2345	Anand	laptop	5	Online	L-4567yh	Asia	India	Andhra pradesh	Sindhu	January
5867	Arun	desktop	8	Vendor	D-2456t	Asia	India	Karnataka	Sindhu	January
8954	Ajay	laptop	4	Institution	L-4567y	Asia	India	Uttar pradesh	Sindhu	January
4698	santosh	Mobile	6	Retail Outlet	insol-2	Asia	India	Kerela	Sindhu	January
6457	Surya	Tablet	2	Online	345ikte	Asia	India	Orissa	Sindhu	January

Faulty Source data:

Code	emp	sale	qty	sale type	model	Region	Month
2345	Anand	laptop	5	Online	L-4567yh	Asia	January
5867	Arun	desktop	8	Vendor	D-2456t	Asia	January
8954	Ajay	laptop	4	Institution	L-4567y	Asia	January
4698	santosh	Mobile	6	Retail Outlet	insol-2	Asia	January
6457	Surya	Tablet	2	Online	345ikte	Asia	January

Now in the faulty source data , fields Region, country, state , reporting are not available

So if i Load the Qlik view application it will throw an error as field not found so inspite of that error I want to load the application as is with a dummy value like 0 in to that field .

Thanks ahead.

Anand.

[sunny_talwar]

I am guessing the data is coming from different files. I would suggest looking into concatenation of the data using for loops instead of doing a wild card load here.

Loops in the Script

[veidlburkhard]

Hi Anand,

in your script load the faulty source data like this:

FaultySource:

Load

      Code,

      emp,

      sale,

      qty,

      sale type,

      model,

      Region,

      Month,

      '-' as country,

      '-' as state,

      '-' as reporting

From yourFaultySource;

Then it will automatically concatenate to your standard source.

Hope this helps.

Burkhard

[imhappiee]

Hi Burkhard,

Thanks for your reply.

That works when I know that in one file if that 3 fields are missing and then can load them as dummy from faulty source

But in my case  say 4 source files are there

source1 may not have country

source2 may not have Region

source3 may not have reporting

source4 may have all fields

any my script in the QV application is ready as to load all the fileds from 4 files

Load

      Code,

      emp,

      sale,

      qty,

      sale type,

      model,

      Region,

      Month,

      country,

      state,

      reporting

From yourFaultySource;

please let me know if i couldn't make you understand the issue. wil try to post more precise example with original data.

Anand

[martinpohl]

I would suggest to create a dummy table.

Then load in a loop all files from your source folder.

Use * to load all fields, independend from the number of fields.

Data:

load * inline [

Code

];

for each file in filelist ('yourfolder\*.csv')

concatenate (Data)

load *

from '$(filelist)' (settings from the sourcedata type)

;

next

Regards

[effinty2112 · Accepted solution]

Hi Anand,

Try:

[Raw Data]:

LOAD *

FROM

[https://community.qlik.com/thread/228627]

(html, codepage is 1252, embedded labels, table is @1);

Concatenate([Raw Data])

LOAD *

FROM

[https://community.qlik.com/thread/228627]

(html, codepage is 1252, embedded labels, table is @2);

NoConcatenate

Data:

LOAD

if(isnull(Code),'Code Missing',Code) as Code,

if(isnull(emp),'Emp Missing',emp) as emp,

if(isnull(sale),0,sale) as sale,

if(isnull(qty),0,qty) as qty,

if(isnull([sale type]),'Sale Type Missing',[sale type]) as [sale type],

if(isnull(model),'Model Missing',model) as model,

if(isnull(Region),'Region Missing',Region) as Region,

if(isnull(country),'Country Missing',country) as country,

if(isnull(state),'State Missing',state) as state,

if(isnull(reporting),'Reporting Missing',reporting) as reporting,

if(isnull(Month),'Month Missing',Month) as Month

Resident [Raw Data];

Drop Table [Raw Data];

good luck

Andrew

[qliksus]

Maybe something like this

OldData:
LOAD * INLINE [
Region,Sales ,Month,Year
A,10 , Jan,2016
B,20 , Feb,2016
C,30, Mar,2016

];

NewData:
LOAD *  INLINE [
Region,Sales
A,10
B,20
C,30

];

for i = 0 to 4
Colfind:
load
if( OldCol<>comingCol ,OldCol)   as OldCol
;
load

FieldName('$(i)','OldData') as OldCol ,
FieldName('$(i)','NewData') as comingCol
AutoGenerate(1) ;

NEXT

Colfindop:
load Concat(DISTINCT '0 ' & ' as ' &OldCol,',') as vars
resident Colfind 
Where len(OldCol)>1;

let VAdd = Peek('vars',0,'Colfindop');

NewData1:
load * ,$(VAdd)

Resident  NewData ;

DROP Table NewData;

[imhappiee]

That was brilliant and it worked, thanks for the quick help

Anand.

[Anonymous]

Hi Anand,

We're glad to see you were able to solve your issue. Please take a moment to Mark Replies as Correct or Helpful to give points to those who helped and to mark the thread as Answered.

Regards,

Qlik Community Team

[imhappiee]

Hi Jeremy Latimer,

I couldn't find the "Correct Answer" option anywhere on this page as per your link, could you please help me get that option enabled . So that I will be happy to mark it as correct Answer. Marking them Helpful for the time being.

Anand