Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

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

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

Codeempsaleqtysale typemodelRegioncountrystatereportingMonth
2345Anandlaptop5OnlineL-4567yhAsiaIndiaAndhra pradeshSindhuJanuary
5867Arundesktop8VendorD-2456t

Asia

IndiaKarnatakaSindhuJanuary
8954Ajaylaptop4InstitutionL-4567yAsiaIndiaUttar pradeshSindhuJanuary
4698santoshMobile6Retail Outletinsol-2AsiaIndiaKerelaSindhuJanuary
6457SuryaTablet2Online345ikteAsiaIndiaOrissaSindhuJanuary

Faulty Source data:

Codeempsaleqtysale typemodelRegionMonth
2345Anandlaptop5OnlineL-4567yhAsiaJanuary
5867Arundesktop8VendorD-2456t

Asia

January
8954Ajaylaptop4InstitutionL-4567yAsiaJanuary
4698santoshMobile6Retail Outletinsol-2AsiaJanuary
6457SuryaTablet2Online345ikteAsiaJanuary

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.

1 Solution

Accepted Solutions
Highlighted
Master
Master

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

View solution in original post

12 Replies
Highlighted

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

Highlighted
Creator III
Creator III

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

Highlighted
Contributor III
Contributor III

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

Highlighted
Partner
Partner

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

Highlighted
Master
Master

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

View solution in original post

Highlighted
Specialist II
Specialist II

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;

Highlighted
Contributor III
Contributor III

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

Anand.

Highlighted
Anonymous
Not applicable

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

Highlighted
Contributor III
Contributor III

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