Reducing modifications

[aitmessaoudali]

Hello community, i have a table with contract to which i join brands by their name, this is the script i used:

Outer Join (contract)

Load contract_id,

if(len(contract_id)>1,'brand1') as MARQUE

FROM [lib://QSData/akusto3.qvd]

(qvd);

Outer Join(contract)

Load contract_id,

if(len(contract_id)>1,'brand2') as MARQUE

FROM [lib://QSData/ecophon3.qvd]

(qvd);

Outer Join (contract)

Load contract_id,

if(len(contract_id)>1,'brand3') as MARQUE

FROM [lib://QSData/master_matrix3.qvd]

(qvd);

Outer Join(contract)

Load contract_id,

if(len(contract_id)>1,'brand4') as MARQUE

FROM [lib://QSData/Plafometal3.qvd]

(qvd);

Outer Join (contract)

Load contract_id,

if(len(contract_id)>1,'brand5') as MARQUE

FROM [lib://QSData/rockfon3.qvd]

(qvd);

Outer Join(contract)

Load contract_id,

if(len(contract_id)>1,'brand6') as MARQUE

FROM [lib://QSData/TEXONA3.qvd]

(qvd);

My problem is i need to change names of brands and of the files regularly and the number of brands might rise as well as decrease depending on the demand of types of clients.

How can automate this process, so i could only make a change only once somewhere?

The table of brands looks like the attached file.

Thanks in advance.

Ali

[achettipalli]

Hi Ali, I bet there'd be multiple solutions using macro or other event Scripts.

Check this looping script like below(also, wouldn't suggest "joins" for those multiple files, can lead to performance issues)

//get the file names

Nfile:

First 1 LOAD FileBaseName() as FileName

FROM

[FolderPath\*.qvd]

(qvd);

Table:

LOAD * Inline [Temp];

// loop to load all files even when all files don't have same number of fields

for i=1 to FieldValueCount('FileName');

let vFileName = FieldValue('FileName',$(i));

Concatenate(Table)

LOAD contract_id,

if(len(contract_id)>1,'vFileName') as MARQUE

FROM

FolderPath\$(vFileName).qvd(qvd);

next

DROP Table Nfile;

DROP Field Temp;

[aitmessaoudali]

hi, what if a have only one file in which i have all all the brands as columns

[achettipalli]

Hi,

If the brands are to be in Rows.

Then you can use Crosstable(): https://community.qlik.com/blogs/qlikviewdesignblog/2014/03/24/crosstable

and consider incremental load if the file updates frequently.

Incremental Load Script