topic Re: Set a dummy value to fields if not present in the source in QlikView

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

imhappiee — Wed, 25 Nov 2020 16:16:04 GMT

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.

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

sunny_talwar — Wed, 10 Aug 2016 14:33:16 GMT

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

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

veidlburkhard — Wed, 10 Aug 2016 15:04:03 GMT

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

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

imhappiee — Wed, 10 Aug 2016 19:46:36 GMT

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

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

martinpohl — Wed, 10 Aug 2016 20:00:45 GMT

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)

;

Regards

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

effinty2112 — Wed, 10 Aug 2016 20:41:25 GMT

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

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

qliksus — Fri, 12 Aug 2016 12:09:59 GMT

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

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;

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

imhappiee — Fri, 12 Aug 2016 14:38:17 GMT

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

Anand.

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

Anonymous — Fri, 12 Aug 2016 15:14:41 GMT

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

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

imhappiee — Tue, 16 Aug 2016 08:19:18 GMT

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

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

Anonymous — Tue, 16 Aug 2016 15:20:44 GMT

Hi Anand,

It sounds like you are viewing the thread in your Activity & Inbox section of your profile, because there are not as many options there. You will need to click on the thread title link to view the actual thread in the community in order to see the Correct Answer button.

Here is a direct link to this thread: Set a dummy value to fields if not present in the source

Please let me know if you are still having trouble.

Best,

Qlik Community Team

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

imhappiee — Tue, 23 Aug 2016 07:45:45 GMT

That helped Jeremy, Thank you

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

effinty2112 — Tue, 23 Aug 2016 08:19:48 GMT

Hi Anand,

Thanks for marking my answer as correct. I appreciate your efforts to do that (and thanks to Jeremy too).

All the best and happy Qliking!

Andrew